Excel functions / Statistics
TRIMMEAN function
The average with a share of the highest and lowest values left out.
Syntax
=TRIMMEAN(array, percent)
Arguments
array: The numbers.percent: The share to leave out in total, half from each end: 0.2 drops the top 10% and the bottom 10%.
When to use it
TRIMMEAN averages the data after dropping a share of the highest and lowest values, so a few outliers do not pull the result. The share is split evenly between the top and the bottom.
Examples
Response times without the extremes
A B C 1 Response (min) 2 3 4.75 3 4 4 4 5 5 6 6 7 30 In C2:
=TRIMMEAN(A2:A7, 0.4)→ 4.7540% of six values is 2.4, rounded down to 2: one from each end.
The plain average for comparison
A B C 1 Response (min) 2 3 8.67 3 4 4 4 5 5 6 6 7 30 In C2:
=TRIMMEAN(A2:A7, 0)→ 8.67
Common mistakes
- Too small a share. The number of values dropped is rounded down to an even number. With few values, a small share may drop none.
TRIMMEAN in other languages
Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.
- Czech: TRIMMEAN
- Danish: TRIMMIDDELVÆRDI
- Dutch: GETRIMD.GEM
- Finnish: KESKIARVO.TASATTU
- French: MOYENNE.REDUITE
- German: GESTUTZTMITTEL
- Hungarian: RÉSZÁTLAG
- Italian: MEDIA.TRONCATA
- Norwegian: TRIMMET.GJENNOMSNITT
- Polish: ŚREDNIA.WEWN
- Portuguese (Brazil): MÉDIA.INTERNA
- Portuguese (Portugal): MÉDIA.INTERNA
- Russian: УРЕЗСРЕДНЕЕ
- Spanish: MEDIA.ACOTADA
- Swedish: TRIMMEDEL
- Turkish: KIRPORTALAMA
Try TRIMMEAN in the playground
Related functions
- AVERAGE: The arithmetic mean of the numbers.
- MEDIAN: The middle number of a sorted list.
- AVERAGEIFS: Averages the cells that meet every one of several conditions.