Average excluding outliers in MS Excel

Your average not making sense? Perhaps you need to average excluding outliers in MS Excel to get a more realistic average.

Averages where outliers give an unrealistic average

As shown below the average is shown as 2.89, but visually we can see that the 65 is throwing out an unrealistic average. Just looking at the numbers the average should be less then 2 and probably significantly less.

averaging excluding outliers in MS excel

In Excel a way around this is to use the TRIMMEAN function

Function for average excluding outliers in MS Excel

So below we have used the TRIMMEAN function in cell B35. As shown the function just needs to know where to look (B2 to B29) and what percentage of the outlier values to exclude (we have referenced to cell D35 to show the logic).

The result of this average is 0.61 instead of the previous average of 2.89 which makes more sense.

average excluding outliers in MS excel

Understanding the % in TRIMMEAN

The only real complexity in TRIMMEAN is what the percentage means.

In its simplest it is how many values to leave out, evenly split from the top and the bottom of the value range. But there are some things to realise about this notably that the % results in a number of numbers to exclude, BUT this is rounded DOWN to the nearest multiple of 2!

So below, when we use 10% (cell D35), this results in a 2.8 (10% of the 28 values in B2 to B29). Excel cannot use 2.8 so it rounds DOWN to the nearest multiple of 2 (cell G35). It now knows to exclude 2 items, the biggest value and the smallest value which is the 65 and any one of the 0’s.

average excluding outliers in ms excel

Depending on the percentage used the number of values excluded from the top and bottom changes BUT always in multiples of 2’s and always rounding DOWN.