Jul4

Harmonic Averages

Tagged with: ,,,,,.

This is a short note to talk about Harmonic Averages. Most people are familiar with Weighted Averages, as they are a valuable tool for aggregation. For instance, with the data below, the average profitability (~1735) can be easily calculated using weighted averages.

Avg_Profit = (1000*1200 + 200*300 + 500*2500 + 10*600 + 100*300) / (1000 + 200 + 500 + 10 + 100)

or

Avg_Profit = SUMPRODUCT(UnitsSold,ProfitPerUnit)/SUM(UnitsSold)

I’m using Excel notation, and assuming it is clear from the context that UnitsSold is a range that covers the second column, for all models, etc.

A less known way of averaging are Harmonic Averages. It is relevant when the data to aggregate is actually a ratio whose denominator is proportional to the weighting factor. A typical case is miles per gallon (MPG) for a bunch of vehicles. Gas consumption is directly proportional to the number of units.

Let’s add some MPG data to the table above.

Using Weighted Averages for an inverse ratio like MPG is plain wrong (24.3 MPG is NOT the average fuel economy)

The right thing is to use Harmonic Average:

Harm_Avg_MPG = (1000 + 200 + 500 + 10 + 100) / (1000/22.5 + 200/15.0 + 500/32.0 + 10/12.0 + 100/24.0)

As Excel doesn’t have a similar function to SUMPRODUCT for adding 1000/22.5, 200/15.0, etc. I will not use Excel notation, but plain math notation:


UPDATED formula

If you have to deal with Harmonic Averages, you may find interesting this note on how to do PivotTable Multidimensional Analysis with Harmonic Averages. There’s a similar one for Weighted Averages as well.

Let me know what you think.

No related posts.

Feed for this Entry Trackback Address

6 Comments to “Harmonic Averages”  

    1
  1. At 7:26 am on July 4, 2008
    Juan Carlos Méndez-García Says:

    Very easy to get confused… Bunnie is an uber-brilliant guy from MIT that has done wonders hacking hardware like the X-Box and others. I really admire his work.
    On bunnie’s blog » Blog Archive » Miles per Gallon vs. Gallons per Mile you see:

    Another seemingly more analytical approach also leads to the same conclusion: 50 + 18 MPG giving a 34 MPG household average seems more efficient than 34 + 28 MPG giving a 31 MPG household average.

    Dude!!! Plain average or Weighted Average doesn’t work. You must use Harmonic Average when using ratios like MPG.

    2. 2
  2. At 7:19 am on July 11, 2008
    Mathias Says:

    Thanks for the refresher! I remember learning about the 3 Pythagorean means way back in high school, but I don’t think I had seen the Harmonic mean mentioned since then. The Geometric mean comes up all the time if you work which growth rates (which is very common in financial calculations); it’s nice to have a good example of why the Harmonic matters, too!

    3. 3
  3. At 4:11 pm on July 14, 2008
    Mathias Says:

    Actually, I believe there is a typo in the formula in math notation; it should be Sum(UnitSold(i)) / Sum(UnitSold(i)/MPG(i))…

    4. 4
  4. At 2:54 pm on July 15, 2008
    Mathias Says:

    It’s me again :)
    Just to let you know that after reading this, I wrote a small Excel User-Defined function to compute the harmonic average… Makes it easier to use!
    Cheers,
    Mathias

    5. 5
  5. At 1:48 pm on July 18, 2008
    Juan Carlos Méndez-García Says:

    @Mathias - You are absolutely correct. I’ll fix the formula on the post to avoid confusion. Thanks very much for the Excel function. As you point out, Excel’s built-in HARMEAN() does not allow for weights.

    6. 6
  6. At 6:15 pm on July 18, 2008
    Juan Carlos Méndez-García Says:

    More harmonic average examples on http://www.nerdblog.com/2008/01/harmonic-mean-mpg-standards.html

Leave a Reply