Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Removing Outliers

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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??

  • #2
    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)

    Comment


    • #3
      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.

      Comment


      • #4
        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)

        Comment

        Trending

        Collapse

        There are no results that meet this criteria.

        Working...
        X