Excel GEOMEAN, TRIMMEAN & HARMEAN — The Averages for When the Mean Lies

TL;DR — When your numbers are growth rates, ratios, or contaminated by outliers, a plain AVERAGE gives a confidently wrong answer, and Excel has a specialist for each case. GEOMEAN is the correct average for anything that compounds — investment returns, growth rates — where =GEOMEAN(1+returns)-1 gives the true average (CAGR) and a plain mean overstates it. TRIMMEAN(range, percent) drops the extreme high and low values before averaging, the way judged competitions throw out the top and bottom scores. HARMEAN is the right average for rates — average speed, P/E ratios, price-per-something. All three exist because the arithmetic mean answers the wrong question for multiplicative and rate data, silently.

=GEOMEAN(1+B2:B6)-1      ' true average annual return (CAGR) from a column of % returns
=TRIMMEAN(A2:A20, 0.2)   ' mean after cutting the top 10% and bottom 10% of values
=HARMEAN(C2:C5)          ' average of rates — e.g. average speed over equal distances

Most "types of average" articles list geometric, trimmed, and harmonic means as trivia — three more buttons next to AVERAGE. They aren't trivia. Each one fixes a specific, common way the plain mean lies: overstating compound growth, getting hijacked by a single outlier, or mishandling rates. Knowing which lie you're facing is the entire skill, so this page is organized by the lie, not by the function.

What you'll learn

Why AVERAGE overstates growth, and how GEOMEAN fixes it (the CAGR pattern)
The GEOMEAN(1+r)-1 trick, and why raw returns need the +1
TRIMMEAN for outlier-resistant averages, and how the cut is split
HARMEAN for rates, and the round-trip-speed example that breaks the plain mean
The ordering law: harmonic ≤ geometric ≤ arithmetic, always
The #NUM! traps: negatives, zeros, and empty ranges

GEOMEAN: the lie about growth

Average a +50% year and a −50% year and the plain mean says 0% — you broke even. You didn't. $100 grows to $150, then falls to $75: you're down 25%. The arithmetic mean of growth rates is simply the wrong operation, because growth multiplies rather than adds, and it always overstates the true result. The geometric mean is the fix — it's the average that, applied period after period, reproduces the actual end value.

The catch is that GEOMEAN multiplies its inputs, so you can't feed it raw percentages like -0.5 (multiplying by a negative is meaningless here, and a 0 would zero everything). You feed it growth factors — each return plus 1 — then subtract 1 at the end to get back to a rate:

' Annual returns in B2:B6 = 50%, -50%, 20%, 10%, -10%
=GEOMEAN(1+B2:B6)-1     ' -> the true average annual return (CAGR)
=AVERAGE(B2:B6)         ' -> 4%   (overstated — ignores compounding)

1+B2:B6 turns each return into a factor (1.5, 0.5, 1.2, …), GEOMEAN finds the single factor that compounds to the same total, and -1 converts it back to a percentage. This is the correct CAGR and it's always ≤ the plain average of the same returns. Any time you're averaging returns, growth rates, or period-over-period multipliers, this is the formula — not AVERAGE. (It's the same compounding logic behind PRODUCT(1+range); GEOMEAN just takes the nth root to turn a total into a per-period average.)

TRIMMEAN: the lie a single outlier tells

One fat-fingered entry or one genuine extreme can drag a mean far from where the bulk of the data sits. TRIMMEAN handles it the way Olympic judging does: throw out the extremes, then average what's left.

=TRIMMEAN(A2:A20, 0.2)   ' discard the top 10% and bottom 10%, average the middle 80%

The second argument is the total fraction to discard, split evenly between the two ends — 0.2 means 10% off the top and 10% off the bottom. So on 20 values, 0.2 removes the 2 highest and 2 lowest and averages the remaining 16. One detail that trips people up: Excel rounds the count to remove down to an even number, so the cut stays symmetric. With 10 values and 0.2, that's 2 to remove (1 each end); the fractional part is floored so it never lops an uneven amount off one side. TRIMMEAN is the honest choice for judged scores, benchmark timings, or any average where you want the typical value rather than one the outliers captured — and unlike deleting rows by hand, it's reproducible and leaves your data intact.

HARMEAN: the lie about rates

Here's the one almost everyone gets wrong. You drive somewhere at 60 mph and back at 40 mph over the same distance. Your average speed is not 50 mph:

' Speeds in C2:C3 = 60, 40
=AVERAGE(C2:C3)    ' -> 50    (WRONG for equal-distance travel)
=HARMEAN(C2:C3)    ' -> 48    (RIGHT: you spend more time at the slower speed)

Because you spend more time at the slower speed, the slow leg weighs more on your overall average, and the true figure is 48. The harmonic mean is the correct average whenever the quantity is a rate and the thing held constant is what the rate is "per" — miles per hour over equal miles, price-to-earnings across equal dollars invested, dollars-per-unit at equal spend. If you're averaging ratios and something in the denominator is what's really equal across rows, reach for HARMEAN, not AVERAGE.

The ordering law: harmonic ≤ geometric ≤ arithmetic

For any set of positive numbers, the three means fall in a fixed order:

=HARMEAN(A2:A10)    <=    =GEOMEAN(A2:A10)    <=    =AVERAGE(A2:A10)

They're only equal when every value is identical; the more spread-out the data, the wider the gap. This isn't just trivia — it's a sanity check. If you computed a "geometric mean" that came out above the arithmetic mean, you've made an error (often feeding GEOMEAN raw returns instead of 1+returns). The ordering law tells you instantly that something is wrong before the number reaches a report.

The #NUM! and error traps

The specialist means are pickier than AVERAGE about their inputs:

GEOMEAN requires all-positive values. A zero or negative returns #NUM! — which is exactly why the growth pattern uses 1+r (factors are positive) rather than the raw rates (which can be negative).
HARMEAN also requires positives and errors on a zero (you can't take 1/0). Rates are naturally positive, so this rarely bites unless a stray blank became a zero.
TRIMMEAN on an empty or all-text range returns #NUM!/#DIV/0! like any average of nothing — guard with IFERROR if the range can be empty.

How ExcelMaster helps

The hard part isn't calling GEOMEAN — it's recognizing that the "average growth rate" you just wrote as =AVERAGE(...) is overstated, or that "average speed" needs a harmonic mean, or that one outlier is quietly running your report. Ask ExcelMaster for "the average annual return on these figures" and it builds =GEOMEAN(1+range)-1, shows you how much the plain mean overstated, and explains why. Say "average these judge scores but ignore the extremes" and it reaches for TRIMMEAN with the right trim fraction instead of you deleting cells by hand.

Frequently asked questions

How do I calculate average growth rate (CAGR) in Excel?

Use the geometric mean of the growth factors: =GEOMEAN(1+B2:B6)-1, where B2:B6 holds the period returns as percentages. Adding 1 turns each return into a factor, GEOMEAN compounds them into a single average factor, and subtracting 1 converts it back to a rate. A plain AVERAGE of the returns overstates the real result because it ignores compounding.

Why is GEOMEAN returning #NUM!?

GEOMEAN multiplies its inputs, so it requires all positive values; a zero or a negative number triggers #NUM!. This is why growth calculations feed it 1+returns (always positive factors) rather than raw returns like -0.1. Check the range for negatives, zeros, or blanks that became zero.

What does the second argument in TRIMMEAN do?

It's the total fraction of data points to discard, split evenly between the highest and lowest. TRIMMEAN(A2:A20, 0.2) removes the top 10% and bottom 10%, averaging the middle 80%. Excel rounds the number removed down to an even count so the trim stays symmetric.

When should I use the harmonic mean instead of AVERAGE?

Use HARMEAN when averaging rates where the denominator quantity is equal across items — average speed over equal distances, average P/E across equal dollar amounts, price per unit at equal spend. In those cases a plain average over-weights the larger rates and gives too high a figure.

Which average should I use?

Use AVERAGE for independent quantities of the same kind. Use GEOMEAN for things that compound (returns, growth rates). Use HARMEAN for rates with an equal denominator (speeds, ratios). Use TRIMMEAN when outliers would distort a plain mean and you want the typical value. For groups of different sizes, use a weighted average instead.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-27.