Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. Established Member
Join Date
5th August 2003
Posts
531

## Removing Outliers

Does anyone out there have a good, easy way of removing outliers from a set of data?

I.E. I want the average of the following, but don't want it skewed by the obvious outliers (clearly 7.77 is an outlier, and I'd say 2.96 is too):

1.138287952
1.211955636
1.135712288
1.153582117
1.16609614
7.770781712
1.228161486
1.261666436
1.210893401
1.167762767
1.164414348
1.223848639
1.315227731
1.271651846
1.22836081
1.255809206
1.308676585
1.185409405
1.172720795
2.960295736

In the past, I've used criteria like:
LowerThreshold < Average(MyData) < HigherThreshold

where I just set tolerance levels (usually as a percentage of the Average)

but this is clearly imperfect as that Average is being affected by the outliers, so using it in criteria isn't very good. Any ideas??

Excel Video Tutorials / Excel Dashboards Reports

2. Resident Old Codger
Join Date
11th February 2003
Location
Near the Land of OZ
Posts
1,563

## Re: Removing Outliers

=(SUM(\$B\$3:\$B\$100)-MAX(\$B\$3:\$B\$100)-MIN(\$B\$3:\$B\$100))/(COUNTA(\$B\$3:\$B\$100)-2)

This takes the max and min out of the total, then sums up the rest, and divides by the count - minus 2, for the Max and Min discarded.

Or if you are only concerned about outliers on the top end, then use the LARGE function, and include as many as you think:

=(SUM(\$B\$3:\$B\$100)-LARGE(\$B\$3:\$B\$100,1)-LARGE(\$B\$3:\$B\$100,2))/(COUNTA(\$B\$3:\$B\$100)-2)

3. Established Member
Join Date
5th August 2003
Posts
531

## Re: Removing Outliers

Thanks for the ideas - I think I might use the LARGE version. The problem is that I don't know how many should/might/will be upper end outliers. But, since I'm usually dealing with relatively close numbers all between 1 and 1.5 (in this application, anyway), I guess throwing out a few too many data in some cases won't hurt me too much when taking an average.

Thanks again.

Excel Video Tutorials / Excel Dashboards Reports

4. Resident Old Codger
Join Date
11th February 2003
Location
Near the Land of OZ
Posts
1,563

## Re: Removing Outliers

You might be able to devise a formula that could test how many. That is, use maybe a 2 STDEV from the Ave, then compare how many are outside that, and have a lookup table that the large function can use. Just an idea.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno