Posted by Eric
September 28, 2016
A common request when analyzing large amounts of data is to evaluate the impact exceptional data has on results. Statistics addresses these needs by offering “median” and “average” when normalizing large numbers of data points.
Median selects a data point in the exact center of all data points to define the “normal” value and, as a result, is unaffected by exceptionally high or low data.
Average, also known as “mean”, on the other hand, sums all of the data points and divides by the number of data points to determine the “normal” value. Average is affected by exceptionally high or low data.
For example, if you have 100 data points, where 97 of them are 100 and the last three values are 1,000, 10,000, and 100,000, which would be “right skewed” data, look at the difference:
● Median value = 100
● Average/Mean value = 1,207
That’s a pretty large discrepancy between the two. Often people will use median to avoid this situation, but there are times when users need to see the impact of these unusual values, which they can’t when using median.
In this workbook you’ll learn how to support both analytical approaches. In one, users need to see outliers and see how it “skews” the results. In another analytical approach the user only wants to see “normal” values that are unaffected by these unusually large or small values.
Walk through this story point by point to take what seems like a simple question, “Do you want to include outliers or not?” and break it down into the numerous steps required to support it. The ability to include or exclude outliers is definitely not as simple as it sounds.
By the time you finish, you’ll be able to replicate this same logic over and over across any number of workbooks. All of the formulas you’ll need are included.
All your users will need to know is how to flip a switch on and off.