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…

Advertisements

3 thoughts on “Rubik’s cube Average formula in Excel

  1. Dear Michiel,

    I have done exactly what you have written and double checked the code – it’s all the same – but when I try to run =cubeavg(5) I get the following #NAME error:

    Ambiguous name detected: cubeavg

    How can I fix this? Do you know what I’ve done wrong?

    Thanks,
    Jai

    1. Hi Jai,

      I think this might be due to security restrictions in Excel. You have to make sure to enable macros: go to file>options, then choose Trust Center, then Macro Setting, and Enable all macros with notification. Also check ‘trust access to the VBA project object model’. After this restart Excel.
      Also when saving a file with macros, make sure to save as Macro Enable Workbook (xlsm), not the regular workbook.

      Good luck
      Michiel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s