Using TRIMMEAN IN Excel To Ignore Outliers – Formula Friday

Written by Barbara ● in Blog,Formula Friday,Formulas

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.

What Next? Want More Tips?

If you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips and check out all of my Formula Friday Blog posts below.

How To Excel At Excel – Formula Friday Blog Posts.

More Excel Tips_New1
Learn Excel With Our Excel Online Courses

 

 

master_728x90


Do You Need Help With An Excel Problem?.

Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help with Excel FAST. Why not check it out?.

ExcelRescue.net

Barbara

Barbara is an experienced data analyst with over 20 years of experience. Author of the How To Excel At Excel Top 50 Excel tips Ebook, YouTuber and Excel Blogger.

Recent Posts

© 2024 Copyright How To Excel At Excel