Archive for April, 2010|Monthly archive page

Rubik’s cube Average formula in Excel

stopwatchAny speedcuber knows how to calculate his or her average: total all, remove fastest and slowest, and divide. Here’s how you do that in Excel

=( SUM(A1:A12) – MIN(A1:A12) – MAX(A1:A12) ) / (COUNT(A1:A12) – 2)

But that is tedious of course, and your constantly changing the range to find the average of e.g. 5, or a running average. So instead I wrote a little VBA function you can put in a module.

  • Press ALT+F11 to go to the editor
  • Choose Insert > Module
  • Choose Insert > Procedure
  • Type CubeAVG
  • Click Function
  • Click OK

Now edit so it’s like the code below

' cubeavg : calculate speedcubing average
Public Function cubeavg(r)
    Dim total As Double, fastest As Double, slowest As Double, n As Integer
    total = WorksheetFunction.Sum(r)
    fastest = WorksheetFunction.Min(r)
    slowest = WorksheetFunction.Max(r)
    n = WorksheetFunction.Count(r) - 2
    cubeavg = (total - slowest - fastest) / n
End Function

Don’t forget to save the file…