Other averages: the trimmed mean and the geometric mean

By the end of this lesson you can work out a trimmed mean with TRIMMEAN and say when it is useful, and work out the average growth rate with GEOMEAN, explaining why the ordinary mean of growth rates gives the wrong answer.

Why it matters

Sometimes the mean is pulled by a few extreme values, but you still want to use most of the data, not just the middle one. And sometimes the values are growth rates: prices up 10 per cent one month and down 5 per cent the next. Averaging growth rates with the ordinary mean is a common mistake in reports. It never makes the growth look worse than it really was, and it can make it look much better.

The idea in plain words

The trimmed mean. In some sports competitions, judges give scores, and the highest and lowest scores are thrown away before averaging, so that one very generous or very harsh judge cannot swing the result. That is a trimmed mean: sort the values, cut off a few at each end, and average the rest.

Amara's 30 days, sorted. The grey dot and the arrow (the festival day) are left out of the trimmed mean. Synthetic data.

Trimmed mean: the mean after leaving out the same number of values at the bottom and at the top. =TRIMMEAN(range, 0.1) leaves out 10 per cent of the values in total, half at each end (rounded down to a whole number at each end).

Growth factor: how much something is multiplied by in one step. Up 10 per cent is a factor of 1.10; down 5 per cent is 0.95.

Geometric mean: the one factor which, used for every step, gives the same end result as the real factors. =GEOMEAN(range). Subtract 1 and multiply by 100 to read it as a percentage growth rate.

See it done

Data, part 1: amara-stall-s1-takings.csv (synthetic). Column D is takings_ngn; Amara's 30 days are rows 2 to 31, and day 20 (row 21) was a festival.

1. The ordinary mean: =AVERAGE(D2:D31) gives ₦36,422, pulled up by the festival day.
2. The median: =MEDIAN(D2:D31) gives ₦32,150.
3. The trimmed mean: =TRIMMEAN(D2:D31, 0.1) gives ₦32,513. With 30 days, 10 per cent is 3 days; that is rounded down to an even number, 2, so 1 day is left out at each end: the smallest day and the festival day.

The trimmed mean sits close to the median, but it still uses 28 of the 30 days, not just the middle one or two.

Data, part 2: made-up numbers. A shop's sales grew by +10%, −5%, +20% and +2% in four months. As growth factors, in cells A2 to A5: 1.10, 0.95, 1.20, 1.02.

1. Where did the sales end up? =PRODUCT(A2:A5) gives 1.2791: after four months, sales are 1.2791 times what they were at the start.
2. The plain average of the rates: =(AVERAGE(A2:A5)-1)*100 gives 6.75 per cent a month. But growing by that rate for four months gives =AVERAGE(A2:A5)^4 = 1.2986: too high.
3. The geometric mean: =(GEOMEAN(A2:A5)-1)*100 gives 6.35 per cent a month. Growing by that rate for four months gives =GEOMEAN(A2:A5)^4 = 1.2791: exactly where the shop really ended up.

Only the geometric-mean rate lands in the right place. Made-up numbers.

Why the plain mean overshoots. Growth multiplies; it does not add. A rise of 20 per cent followed by a fall of 20 per cent does not bring you back to the start: 1.20 × 0.80 = 0.96, a loss of 4 per cent. The geometric mean is built from multiplying, so it gets this right. TRIMMEAN, GEOMEAN and PRODUCT work the same way in Google Sheets and Excel.

Your turn, with help

A made-up savings account grew by 5 per cent in year 1, 15 per cent in year 2 and 1 per cent in year 3. What was the average yearly growth rate? Give 2 decimal places.

Hints: The growth factors are 1.05, 1.15 and 1.01. Put them in three cells and use =(GEOMEAN(...)-1)*100.

Show the answer

6.84 per cent a year. Check: =GEOMEAN(1.05, 1.15, 1.01)^3 gives 1.219575, the same as 1.05 × 1.15 × 1.01 = 1.219575. The plain average of the rates, 7.00 per cent, is slightly too high.

Your turn, alone

1. penguins.csv (real), column F is body_mass_g. Compare =AVERAGE(F2:F345) with =TRIMMEAN(F2:F345, 0.1). Are they close or far apart? What does that tell you?
2. Kofi's days are rows 32 to 38 of the takings file. Find =TRIMMEAN(D32:D38, 0.3). How many days does it leave out at each end?
3. A price goes up 50 per cent one year and down 50 per cent the next. Kofi says: "The average change is 0 per cent, so the price is back where it started." Is he right? Use the growth factors to explain.

Watch out

• Trimming is not deleting. The trimmed mean leaves values out of one calculation only. Never delete awkward values from the data itself, and always say that you used a trimmed mean and how much you trimmed.
• GEOMEAN needs factors, not percentages. Type 1.10, not 10 per cent or 0.10. GEOMEAN cannot use zero or negative numbers, and a fall of 5 per cent is the factor 0.95, which is fine.
• The median, trimmed mean and mean answer slightly different questions. For a report, pick one, say which, and say why.

Say it back

1. What does TRIMMEAN(range, 0.2) do with 20 values?
2. What is the growth factor for "down 8 per cent"?
3. Why is the ordinary mean of growth rates misleading?

Show the answers

1. It leaves out 20 per cent of 20 values, which is 4: the 2 smallest and the 2 largest. Then it averages the other 16.
2. 0.92.
3. Growth multiplies rather than adds, so the plain mean of the rates overstates the growth; the geometric mean gives the one rate that really leads to the end result.

What is next

You now have a whole toolbox of centres: mean, median, mode, weighted, trimmed and geometric. Lesson 9 brings it together: a simple way to choose the centre that tells the truth about your data.

Formulas in this lesson were checked on 2026-10-11 against the official Google Sheets and Microsoft Excel help pages (TRIMMEAN, GEOMEAN, PRODUCT), and every number was calculated by running the formulas in a spreadsheet program (LibreOffice Calc) and checked by a separate calculation. Menu steps are written for Google Sheets on the web and Excel for Microsoft 365.

---

Answers to Your turn, alone

Show the answers

1. =AVERAGE(F2:F345) gives 4,201.75 g and =TRIMMEAN(F2:F345, 0.1) gives 4,178.81 g. They are close, so no extreme values are pulling the mean far.
2. =TRIMMEAN(D32:D38, 0.3) gives ₦34,700. 30 per cent of 7 days is 2.1; rounded down to an even number that is 2, so 1 day is left out at each end.
3. No. The factors are 1.5 and 0.5, and 1.5 × 0.5 = 0.75: the price ends 25 per cent lower than where it started. The geometric mean of the factors is about 0.866, a fall of about 13.4 per cent a year.