Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Removing Outliers

  1. #1
    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. #2
    Join Date
    11th February 2003
    Location
    Near the Land of OZ
    Posts
    1,591

    Re: Removing Outliers

    How about this:

    =(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)

    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt

    Old, slow, and confused - but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3.28.2008)

  3. #3
    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. #4
    Join Date
    11th February 2003
    Location
    Near the Land of OZ
    Posts
    1,591

    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.

    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt

    Old, slow, and confused - but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3.28.2008)

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Csv Removing 0's
    By cbanks in forum EXCEL HELP
    Replies: 1
    Last Post: July 18th, 2007, 03:27

Bookmarks

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