How to Calculate the Harmonic Mean in Excel

Key Insights

• The harmonic mean is the correct average for rates and ratios—using arithmetic mean for speeds, prices per unit, or P/E ratios will give you wrong answers
• Excel’s HARMEAN function fails on zeros and negative numbers, so real-world data requires filtering formulas like =HARMEAN(IF(A1:A10>0,A1:A10))
• For weighted scenarios like portfolio P/E ratios, use =SUMPRODUCT(weights)/SUMPRODUCT(weights/values) instead of the built-in function

Introduction to Harmonic Mean

The harmonic mean is the average you should be using but probably aren’t. While the arithmetic mean dominates spreadsheet calculations, it gives incorrect results when averaging rates, ratios, or any values expressed as “something per something else.”

Here’s the core principle: use harmonic mean when the numerator stays constant across your values. Speeds (distance per time with fixed distance), prices per unit (cost per item with fixed spending), and financial ratios like P/E (price per earnings) all fit this pattern.

Consider driving 60 mph for the first half of a trip and 40 mph for the second half. The arithmetic mean suggests 50 mph average. But if the trip is 120 miles total, you spend 1 hour on the first 60 miles and 1.5 hours on the last 60 miles—2.5 hours for 120 miles equals 48 mph. That’s the harmonic mean.

The three Pythagorean means relate as: Harmonic ≤ Geometric ≤ Arithmetic. They’re equal only when all values are identical. The greater the variance in your data, the more the harmonic mean drops below the arithmetic mean.

The HARMEAN Function

Excel provides HARMEAN as a built-in function. The syntax is straightforward:

=HARMEAN(number1, [number2], ...)

For a contiguous range of values:

=HARMEAN(A1:A10)

You can also pass individual values or mixed references:

=HARMEAN(A1:A5, B1:B3, 25, 30)

HARMEAN accepts up to 255 arguments, where each argument can be a single value, a range, or an array. The function ignores text and logical values within ranges but throws errors if you pass them directly as arguments.

A practical example—calculating average fuel efficiency across multiple vehicles:

| A              | B           |
|----------------|-------------|
| Vehicle        | MPG         |
| Sedan          | 32          |
| SUV            | 24          |
| Truck          | 18          |
| Compact        | 38          |
| Average MPG    | =HARMEAN(B2:B5) |

The result is 26.1 MPG, not the arithmetic mean of 28 MPG. If you drove each vehicle the same distance, you’d consume fuel at a rate corresponding to 26.1 MPG—not 28.

Manual Calculation Method

Understanding the underlying formula helps when HARMEAN isn’t available or when you need more control over the calculation. The harmonic mean formula is:

H = n / (1/x₁ + 1/x₂ + ... + 1/xₙ)

In Excel, translate this to:

=COUNT(A1:A10)/SUMPRODUCT(1/A1:A10)

Breaking this down:

• COUNT(A1:A10) gives you n, the number of values
• SUMPRODUCT(1/A1:A10) calculates the sum of reciprocals
• Division yields the harmonic mean

For non-contiguous data or explicit values:

=3/(1/60+1/40+1/30)

This returns 40—the harmonic mean of 60, 40, and 30.

The manual method becomes essential when building more complex calculations like weighted harmonic means or when working in environments without HARMEAN (older Excel versions, Google Sheets before 2020, or other spreadsheet applications).

You can also use AVERAGE with reciprocals:

=1/AVERAGE(1/A1:A10)

This is mathematically equivalent and sometimes easier to read, though it requires the reciprocal operation twice.

Handling Edge Cases

HARMEAN has strict requirements that will break your formulas with real-world data. The function returns #NUM! for any zero or negative value in the range. This makes sense mathematically—you can’t take the reciprocal of zero, and negative values would create sign issues in the sum of reciprocals.

To filter out zeros, use an array formula:

=HARMEAN(IF(A1:A10>0,A1:A10))

In Excel 365 or Excel 2021, this works as a regular formula. In older versions, enter it with Ctrl+Shift+Enter to make it an array formula.

For handling both zeros and empty cells explicitly:

=HARMEAN(IF((A1:A10>0)*(A1:A10<>""),A1:A10))

If your data might contain negative values that you want to include (rare for harmonic mean use cases, but possible), you’ll need the manual calculation approach with absolute values or a different averaging method entirely.

A more robust solution using FILTER (Excel 365):

=HARMEAN(FILTER(A1:A10, A1:A10>0))

This is cleaner and more readable than the IF-based array formula.

For error handling when the entire range might be zeros or empty:

=IFERROR(HARMEAN(IF(A1:A10>0,A1:A10)), "No valid data")

Practical Use Case: Average Speed Calculation

Let’s build a complete worksheet for the classic harmonic mean problem: calculating average speed for a trip with multiple segments.

Scenario: A delivery driver makes a round trip. They drive 30 miles to the destination at 45 mph (traffic), then return the same 30 miles at 60 mph (clear roads). What’s their average speed?

Set up the worksheet:

| A              | B        | C           | D              |
|----------------|----------|-------------|----------------|
| Segment        | Distance | Speed (mph) | Time (hours)   |
| Outbound       | 30       | 45          | =B2/C2         |
| Return         | 30       | 60          | =B3/C3         |
| Total          | =SUM(B2:B3) | | =SUM(D2:D3)    |
|                |          |             |                |
| Arithmetic Mean|          | =AVERAGE(C2:C3) |             |
| Harmonic Mean  |          | =HARMEAN(C2:C3) |             |
| Verification   |          | =B4/D4      |                |

Results:

• Arithmetic Mean: 52.5 mph (wrong)
• Harmonic Mean: 51.43 mph (correct)
• Verification (Total Distance / Total Time): 51.43 mph

The verification formula proves the harmonic mean is correct. The driver traveled 60 miles in 1.167 hours (0.667 + 0.5), yielding 51.43 mph average.

Now consider unequal distances—the driver goes 20 miles at 45 mph and returns 20 miles at 60 mph, but also makes a 40-mile highway segment at 70 mph:

| A              | B        | C           |
|----------------|----------|-------------|
| Segment        | Distance | Speed (mph) |
| City outbound  | 20       | 45          |
| City return    | 20       | 60          |
| Highway        | 40       | 70          |
| Total          | 80       |             |
|                |          |             |
| Weighted Avg   |          | =B5/SUMPRODUCT(B2:B4/C2:C4) |

The weighted harmonic mean formula =B5/SUMPRODUCT(B2:B4/C2:C4) returns 58.33 mph. This accounts for spending more time at lower speeds even though the highway segment was longer.

Weighted Harmonic Mean

Standard HARMEAN assumes equal weights, but many applications require weighting. The weighted harmonic mean formula is:

Hw = Σwᵢ / Σ(wᵢ/xᵢ)

In Excel:

=SUMPRODUCT(weights)/SUMPRODUCT(weights/values)

The classic application is calculating a portfolio’s weighted average P/E ratio. If you own stocks with different market caps, each stock’s P/E should be weighted by how much of your portfolio it represents.

| A        | B           | C         |
|----------|-------------|-----------|
| Stock    | Market Cap  | P/E Ratio |
| AAPL     | 50000       | 28        |
| MSFT     | 30000       | 32        |
| GOOGL    | 20000       | 22        |
|          |             |           |
| Arithmetic Avg P/E | | =AVERAGE(C2:C4) |
| Weighted Harmonic  | | =SUM(B2:B4)/SUMPRODUCT(B2:B4/C2:C4) |

Results:

• Arithmetic Average: 27.33
• Weighted Harmonic Mean: 27.18

The difference seems small here, but with more varied P/E ratios or weights, the gap widens significantly. Using arithmetic mean for P/E ratios systematically overstates the portfolio’s valuation.

For equal-weighted index calculations where you’re averaging across many stocks:

=COUNT(C2:C100)/SUMPRODUCT(1/C2:C100)

This gives the harmonic mean P/E, which better represents what you’d pay per dollar of earnings across the index.

Summary

Quick reference for harmonic mean calculations in Excel:

Choose harmonic mean when averaging rates where the numerator is fixed: speeds over equal distances, prices when spending equal amounts, efficiency ratios, and financial multiples. When in doubt, ask yourself: “Am I averaging things that are ‘per’ something?” If yes, harmonic mean is likely correct.

The arithmetic mean remains appropriate when values are additive (totals, counts, measurements). The geometric mean fits multiplicative relationships (growth rates, compound returns). Match your mean to your data’s mathematical structure, and your analyses will be accurate.