Using TRIMMEAN IN Excel To Ignore Outliers - Formula Friday

Hello, Excellers welcome to another #formulafriday blog post with yet another an awesome Excel tip. No – one likes to be ignored right?….wrong?…let you decide that one, but I was asked recently about calculating an average of a data set which was being skewed by some very rogue or outlying looking values in the range and data. Today we are going use TRIMMEAN to Ignore Outliers.. The values could have been extracted or input incorrectly, but it did affect the average of the data set. Here is a sanitised version of the data to work through this example and tip.

Have you signed up to the Excel Newsletter?. 3 free Excel tips The first Wednesday of every month.? Sign up in the box to the right or in the link at the bottom of this blog post. See you next Month!.

The data represents daily revenue from Advertisements on a web page – 5 days over a 4 week period. (Monday to Friday) – 20 records in total.

The average revenue when calculated on this data set is 22.89. If we compare that using a trimmed mean or TRIMMEAN, that calculation results in a value of 20.97.

TRIM WHAT?. well using the TRIMMEAN we can exclude a number of outlying data points in our data. We can specify a percentage of data points both at the top and bottom of our values that may be affecting the results. Judges scores in Ice Skating, very top and very bottom scores are disregarded – this is a great example of this

TRIMMEAN has simple enough syntax with two required arguments

TRIMMEAN(array, per cent)

array -The array or range of values to trim and average.

per cent – The fractional number of data points to exclude from the calculation.

Back to our example, how does the TRIMMEAN affect our data?.

For example, if we choose to remove the top 25 and bottom 25 %. Percent = 0.25, 5 points are trimmed from a data set of 20 points (20 x 0.25) to give 5. Divide this by 2 to get the number of points to trim from the top and bottom values. TRIMMEAN then rounds down the number of data points to be excluded to the nearest multiple of 2.

So we will be removing 2 data points from both the top and bottom. See the effect below.

We can easily work our formula back and check the validity of this calculation by manually looking at the average of the daily advertisement revenue by excluding the top and bottom 2 values.