The Excel TRIMMEAN function calculates the trimmed mean (or truncated mean) of a supplied set of values.
Synatx: 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 percentage is the total percentage of values to be excluded. 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. The spreadsheet on the left shows the format of the functions and the spreadsheet on the right shows the results.
Note that, in cell B1 of the spreadsheet above, the 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.
Trimmean Function Errors
If you get an error from the Excel Trimmean function this is likely to be one of the following:
|#NUM!||–||Occurs if the supplied percent argument is < 0 or ≥ 1|
|#VALUE!||–||Occurs if the percent argument cannot be interpreted as a numeric value|