TrimMean() function is a slightly different and useful measure than the regular Excel Average(). It’s still an average/mean but without the high and low, extreme or outlier values in the list.
Average() (and its partners AverageA() and AverageIFS() use all the values in the array or list provided. TrimMean() excludes the highest and lowest extreme values that may skew the average.
In other words, TrimMean() is a quick way to exclude outliers. In a list of exam results, it can give a better measure of how most students fared excluding the smarties and the dummies.
It is particularly useful for larger lists of values. Perhaps when the results are subjective (results of a survey) were some people give very high or low scores. Or you want to exclude very good or bad results, like unusual sales events.
How TrimMean() works
Give Excel the list of values to average plus a percentage to trim off each end of the sorted list.
TRIMMEAN(array, percent)
Array – range of values to trim then average
Percent – the percentage to trim from high/low ends of the range. As a fractional number not a % Trim 10% using 0.1 30% with 0.3
The percentage is the overall proportion of values removed, halve the percentage for the number removed from each end. For a TrimMean() percentage of 0.2 or 20% from a data set of 12 points (12 x 0.2) = 2.4, two values are trimmed, one from the top and one from the bottom.
TrimMean() always removes the same number of values from the high and low end of the list. To do that, it rounds the number of values to remove to the nearest even number. If the number of values is an odd number (e.g 5), TrimMean() rounds down (4) then halves for the total to be removed from each end (2).
TrimMean() in action
In the below example, the formula for TRIMMEAN() 20% is
=TRIMMEAN(C4:C15, 0.2)
or
=TRIMMEAN(C4:C15, 20%)
20% and 0.2 mean the same thing to Excel
This provides the average for the given range of data, while excluding 10% of the data from which each end, a total of 20%.
The formula in C17 below excludes the first value (75) and the last value (2,889).
You need to make sure to be extra cautious when using smaller number of values or small percent.
For example, a TRIMMEAN() percent of 0.1 will provide the same results as AVERAGE() for our 2020 sales data.
TrimMean() tricks
The list of values doesn’t have to be a single range. You can give the formula individual cell or range references with the last parameter as the percentage to trim.
TrimMean(C2, D6, E3:E45, 20%) combines the values from the two cell references and one range given before trimming by 20%.
Up to 255 cell references can be added.
Zero to trim()
If Zero is the percentage to trim, TrimMean() returns the same as Average()
TrimMean(C4:C15,0) is the same as Average(C4:C15)
That means you can have the trim percentage in another cell to be changed interactively.
Leaving out the last parameter/percentage doesn’t break TrimMean(), it still works as Average().
TrimMean(C4:C15) is the same as Average(C4:C15) or TrimMean(C4:C15,0)
Looks easy but it’s not
TrimMean() is a simple idea but there’s some fearsome programming behind it. See what it took Greg Deckler to replicate TrimMean() in PowerQuery.
Average / Mean options in Excel
BMI calcs can do more in Excel
Beware the date calculation trap in Excel
Excel’s Rank/Rank.EQ function to show order, first, second etc.
Excel rankings with ordinal numbers, joint, equal and more
.
