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:

• A percentage of 15%, is 1.5 values, which will be rounded down to 0 (i.e. no values are discarded from the array before calculating the mean);
• A percentage of 20%, is 2 values, so 1 value will be discarded from each end of the range before calculating the mean of the remaining values;
• A percentage of 25%, is 2.5 values, which will be rounded down to 2 (i.e. 1 value will be discarded from each end of the range before calculating the mean of the remaining values).

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:
AB
10.5=TRIMMEAN( A1:A10, 15% )
26=TRIMMEAN( A1:A10, 20% )
37=TRIMMEAN( A1:A10, 40% )
47
58
68
79
89
916
1024
Results:
ABC
10.59.45- No values omitted
268.75- 2 values omitted (1 at each end)
378- 4 values omitted (2 at each end)
47
58
68
79
89
916
1024

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 ≥ 1orThe supplied array argument is empty. #VALUE! - Occurs if the supplied percent argument is non-numeric.