TRIMMEAN function
TRIMMEAN returns an average that leaves out extreme values at both ends. It suits cases like judged scores where the highest and lowest are removed.
Example
Read it as a sentence: average the values in A1:A8 after dropping the top and bottom 25 percent, which gives 4.833333333.
Excel shows 4.833333333
A
1
2
2
4
3
4
4
4
5
5
6
5
7
7
8
9
Data
Syntax
TRIMMEAN(array, percent)
-
array(required) - The data.
-
percent(required) - Fraction of points to drop in total, split evenly between top and bottom.
Returns: The mean after removing extreme values.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=TRIMMEAN({1,2,3,4,5,6,7,8,9,100},0.2) | 5.5 | 20% trim |
=TRIMMEAN(Data!A1:A8,0.25) | 4.833333333 | Range 25% |
=TRIMMEAN(Data!A1:A8,0) | 5 | Percent 0 |
=TRIMMEAN(Data!A1:A8,0.3) | 4.833333333 | Odd trim rounds down |
=TRIMMEAN(Data!B1:B8,0.2) | 26.66666667 | Mixed range |
=TRIMMEAN(Data!A1:A8,{0,0.5}) | 5 | Array percent (spills) |
=TRIMMEAN(Data!A1:A8,1) | #NUM! | Percent 1 |
=TRIMMEAN(Data!A1:A8,-0.1) | #NUM! | Negative percent |
The Data sheet these formulas read
A cell that starts with = holds that formula.
A
B
1
2
10
2
4
20
3
4
TRUE
4
4
5
5
abc
6
5
30
7
7
FALSE
8
9
40
Data
Watch out for
- Dropped count is rounded down to an even number.
- percent is a fraction like 0.2, not 20.
Where it works
- Excel
- TRIMMEAN is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .