Harmonic average function14 Jul 2008
I was just reading this post on Juan Carlos Méndez-García’s blog, where he describes when and how to use harmonic averages. I hadn’t seen that average in a long while, and thought his example provided a good illustration as to why this seemingly odd way to compute averages would make sense. Practically, there is one issue, though: Excel doesn’t come up with a built-in Harmonic Average function. I thought I would give a shot at writing a user-defined function that does just that. The function I wrote mimics SUMPRODUCT(), but is called HarmonicAverage, and takes 2 ranges as arguments. The first range is the weight of each observation, the second the value of the observation.
The worksheet attached (HarmonicAverage.zip (7.63 kb)) illustrates the function in action, on Juan Carlos’ example. If you want to use it in your own workbook, the best way to go is to follow these steps:
- Open your workbook, and go to Tools > Macros > Visual Basic Editor:
- On the left-hand side, right-click on VBA Project (Your Workbook name), and select “Import File”:
Navigate to the file “Module1.bas” (attached with this post, Module1.bas (1.47 kb)) and select “Open”; your should now see a folder “Modules” on the left-hand side of your screen.
Close the editor, and go back to your workbook. Select a cell.
Go to Insert > Function > select the category “User Defined” > select “HarmonicAverage”. You should now see a window, where you can select the range that contains the units, and the range that contains the values.
That’s it! Once you click OK, it should compute the Harmonic Average.
I attached below the code of the function. In spite of the weird syntax highlighting, it is written in VBA - I just could not get the syntax to work (If anyone knows how to get BlogEngine.Net to properly format VB and/or VBA code, he/she would earn my gratitude!).
Function HarmonicAverage(unitsRange As Range, valuesRange As Range) As Double Dim numberOfItems As Integer numberOfItems = unitsRange.Rows.Count Dim numberOfValues As Integer numberOfValues = valuesRange.Rows.Count 'Validate that the ranges have same size If (numberOfItems <> numberOfValues) Then HarmonicAverage = Error Else Dim units As Double Dim value As Double Dim totalUnits As Double Dim denominatorValue As Double Dim totalDenominator As Double ' Iterate over the items in the ranges For Item = 1 To numberOfItems units = unitsRange.Cells(Item, 1) value = valuesRange.Cells(Item, 1) ' Guard for 0 values If (value > 0) Then denominatorValue = units / valuesRange.Cells(Item, 1) Else denominatorValue = 0 End If totalUnits = totalUnits + units totalDenominator = totalDenominator + denominatorValue Next If (totalDenominator > 0) Then HarmonicAverage = totalUnits / totalDenominator Else HarmonicAverage = Error End If End If End Function
(HarmonicAverage.zip (7.63 kb) Module1.bas (1.47 kb)
Have a comment or a question? Ping me on Twitter, or use the comments section!