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:
1. Open your workbook, and go to Tools > Macros > Visual Basic Editor:
2. 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.
3. Close the editor, and go back to your workbook. Select a cell.
4. 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
Dim units As Double
Dim totalUnits As Double
Dim denominatorValue As Double
Dim totalDenominator As Double
' Iterate over the items in
For Item = 1 To numberOfItems
units = unitsRange.Cells(Item, 1)
= valuesRange.Cells(Item, 1)
' Guard for
> 0) Then
denominatorValue = units / valuesRange.Cells(Item, 1)
denominatorValue = 0
totalUnits = totalUnits + units
totalDenominator = totalDenominator + denominatorValue
If (totalDenominator > 0) Then
HarmonicAverage = totalUnits / totalDenominator
HarmonicAverage = Error
HarmonicAverage.zip (7.63 kb)
Module1.bas (1.47 kb)