Harmonic Mean (Rates & Ratios)

Excel Formulas › Statistics

All versionsHARMEAN

For averaging rates — speeds, prices per unit, P/E ratios — the plain mean overstates. The harmonic mean is the right average when the quantity is a ratio with a fixed numerator.

Quick formula: harmonic mean of B2:B100:
=HARMEAN(B2:B100)
It’s the reciprocal of the average of reciprocals — always ≤ the arithmetic mean.

Functions used (tap for the full reference guide):

HARMEAN

The example

Average speed over equal distances.

AB
1TripSpeed
2Out60
3Back40
4Harmonic mean48

The formula

The formula:

=HARMEAN(B2:B100) // 60 & 40 over equal distance → 48, not 50

How it works

How it works:

  1. HARMEAN computes n / (sum of 1/each value).
  2. For two equal-distance trips at 60 and 40, the true average speed is 48 (not the arithmetic 50), because you spend more time at the slower speed.
  3. Use it whenever you’re averaging a rate over equal amounts of the rate’s denominator.
  4. It’s always less than or equal to the arithmetic mean; the gap grows with spread.

Three means, three jobs: arithmetic for sums, geometric for growth/compounding, harmonic for rates. Picking the wrong one is a classic averaging error — ask “average of what?”

Try it: interactive demo

Live demo

Values (e.g. speeds).

Harmonic · Arithmetic

Variations

Arithmetic (compare)

Overstates for rates:

=AVERAGE(B2:B100)

Geometric mean

For growth rates:

=GEOMEAN(B2:B100)

Weighted harmonic

Unequal distances:

=SUM(w)/SUMPRODUCT(w, 1/rates)

Pitfalls & errors

No zeros or negatives. HARMEAN needs positive values — a zero divides by zero.

Only for rates. Don’t use it for additive quantities; the arithmetic mean is right there.

Equal weights assumed. For unequal denominators, use a weighted harmonic mean.

Practice workbook

📊
Download the free Harmonic Mean (Rates & Ratios) practice workbook
A harmonic-mean sheet with arithmetic, geometric, and weighted variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate the harmonic mean in Excel?
Use =HARMEAN(range). It's the right average for rates (like speeds over equal distances) and is always less than or equal to the arithmetic mean.
When should I use harmonic mean instead of average?
When averaging a rate over equal amounts of its denominator — e.g. average speed over equal distances, or average price per unit for equal spend.
Why can't HARMEAN have zeros?
It sums the reciprocals (1/value), so a zero causes a divide-by-zero error. All values must be positive.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Geometric mean · Weighted average · Coefficient of variation

Function references: HARMEAN