Home » Excel-Built-In-Functions » Excel-Statistical-Functions » Excel-Trimmean-Function

Excel Trimmean Function

Trimmed Mean

The Trimmed Mean (also known as the truncated mean) is a measure of mean that indicates the central tendancy of a set of values.

The Trimmed Mean is calculated by discarding some of the values at either end of the range of values, before calculating the arithmetic mean of the remaining values. This prevents the calculated mean being skewed by extreme values (also known as outliers).

For further information on the Trimmed Mean, see the Wikipedia Truncated Mean page
Related Function:
AVERAGE function

Function Description

The Excel TRIMMEAN function calculates the trimmed mean (or truncated mean) of a supplied set of values.

The syntax of the function is:

TRIMMEAN( array, percent )

where the function arguments are:

array - An array of numeric values, for which you want to calculate the trimmed mean.
percent - The percentage of values that you want to be discarded from the supplied array.


Note that the specified percent value is the total percentage of values to be excluded from the calculation. This percentage is divided by two, to get the number of values that are removed from each end of the range.

It should also be noted that, when Excel is calculating how many values to discard from the supplied array of values, the calculated percentage is rounded down to the nearest multiple of 2. For example, if you want to calculate the trimmed mean of an array of 10 values, then:


Trimmean Function Example

Cells B1-B3 of the spreadsheet below show 3 examples of the Excel Trimmean Function, all of which are used to calculate the trimmed mean of the values in cells A1-A10, for different percent values.

 Formulas:
  A B
1 0.5 =TRIMMEAN( A1:A10, 15% )
2 6 =TRIMMEAN( A1:A10, 20% )
3 7 =TRIMMEAN( A1:A10, 40% )
4 7  
5 8  
6 8  
7 9  
8 9  
9 16  
10 24  
 Results:
  A B C
1 0.5 9.45 - No values omitted
2 6 8.75 - 2 values omitted (1 at each end)
3 7 8 - 4 values omitted (2 at each end)
4 7    
5 8    
6 8    
7 9    
8 9    
9 16    
10 24    

Note that, in cell B1 of the spreadsheet above, the supplied percent argument is 15%. As there are 10 values in the supplied array, the number of values to be ignored is 1.5 rounded down to the nearest multiple of 2 which is zero.


Further examples of the Excel Trimmean function are provided on the Microsoft Office website.


Trimmean Function Errors

If you get an error from the Excel Trimmean function this is likely to be one of the following:

Common Errors
#NUM! -

Occurs if either:

  • The supplied percent argument is < 0 or ≥ 1
or
  • The supplied array argument is empty.
#VALUE! - Occurs if the supplied percent argument is non-numeric.
Return to the Excel Statistical Functions Page
Return to the List of All Built-In Excel Functions