Results 1 to 3 of 3

Math Help - Addressing Outliers in Excel

  1. #1
    Newbie
    Joined
    Mar 2007
    Posts
    2

    Addressing Outliers in Excel

    Hello,

    When evaluating a set of items for their popularity, I pull popularity

    metrics from two different sources, aggregating each score into an average.

    While I am satisfied with this metric (Let's call it the 'popularity

    score'), I'd like to create a simplified, more meaningful score.

    My initial though was to create an index using the following formula:

    (D3/MAX($D:$D))*100)

    Where D3 is the individual popularity score and D represents all

    popularity scores.

    I discovered, however, that outliers would have a disruptive effect on the

    data. For instance, if there where several VERY popular items, other items

    with acceptable popularity scores would appear very low in relative terms.

    Any ideas on how to account for the Outliers while still retaining the

    meaning in an easy to understand way?
    Follow Math Help Forum on Facebook and Google+

  2. #2
    Senior Member
    Joined
    Apr 2006
    Posts
    401
    Quote Originally Posted by stupidpoeticjustice View Post
    Hello,

    When evaluating a set of items for their popularity, I pull popularity

    metrics from two different sources, aggregating each score into an average.

    While I am satisfied with this metric (Let's call it the 'popularity

    score'), I'd like to create a simplified, more meaningful score.

    My initial though was to create an index using the following formula:

    (D3/MAX($D:$D))*100)

    Where D3 is the individual popularity score and D represents all

    popularity scores.

    I discovered, however, that outliers would have a disruptive effect on the

    data. For instance, if there where several VERY popular items, other items

    with acceptable popularity scores would appear very low in relative terms.

    Any ideas on how to account for the Outliers while still retaining the

    meaning in an easy to understand way?
    Why not take the lognormal distribution and then take the antilog- this will take care of outliers.
    Follow Math Help Forum on Facebook and Google+

  3. #3
    Newbie
    Joined
    Mar 2007
    Posts
    2
    Thanks for the reply. First off, I believe that this would imply that there was a normal distribution. I believe that this may not be the case.

    Regarding implementation, I'm having difficulty getting the formula. Here is my data (D329):

    0.00
    0.00
    0.00
    42.56
    0.00
    680.99
    0.00
    0.00
    60.80
    0.00
    18.24
    0.00
    85.12
    170.24
    0.00
    0.00
    297.92
    103.36
    0.00
    0.00
    0.00
    30.40
    18.24
    30.40
    72.96
    0.00

    Thanks again for your help!
    Follow Math Help Forum on Facebook and Google+

Similar Math Help Forum Discussions

  1. Outliers
    Posted in the Statistics Forum
    Replies: 3
    Last Post: May 27th 2011, 11:41 AM
  2. minitab, outliers, transformations...
    Posted in the Advanced Statistics Forum
    Replies: 0
    Last Post: November 15th 2010, 07:19 AM
  3. The affect of outliers.
    Posted in the Advanced Statistics Forum
    Replies: 1
    Last Post: December 6th 2008, 01:34 AM
  4. Outliers Explanation
    Posted in the Math Topics Forum
    Replies: 3
    Last Post: November 3rd 2008, 09:59 PM
  5. identify any outliers
    Posted in the Algebra Forum
    Replies: 1
    Last Post: April 4th 2007, 12:53 PM

Search Tags


/mathhelpforum @mathhelpforum