Addressing Outliers in Excel
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:
Where D3 is the individual popularity score and D:D represents all
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?