Highlighting Outliers in your Data with the Tukey Method

I just recently completed a project that required a set of “Rank and Stack” reports for the purposes of identifying poor performers and recognizing good performers. In the process, it became clear that some of the data points had values that were so far removed from the norm that they were throwing off the ranking. So I had to go through an exercise of highlighting the outliers.

.

There are lots of statistical methods for identifying outliers. I used John Tukey’s method of leveraging the Interquartile Range. His method is applicable to most ranges since it isn’t dependent on distributional assumptions. It also ignores the mean and standard deviation, making it resistant to being influenced by the extreme values in the range.

.

Because I have no life, I thought it would be fun to explain Tukey’s method, and how to use it in Excel. So let me put my retainer in my mouth and let’s get started.

.


Understanding Interquartile Ranges

When performing data analysis, we assume our values cluster around some central data point (a median). But sometimes, a few of the values fall too far from the central point – skewing the analysis. These values are called outliers (they lay outside the expected range).

Let’s use these numbers and try to find the outliers.

.

The first step in identifying outliers is to pinpoint the statistical center of the range. To do this, we start by finding the 1st and 3rd Quartiles. If you’re not familiar with Quartiles, I explain them in detail in another post.

The 1st Quartile in this range is 43.5 – the 3rd Quartile is 66.75

.

Next, we subtract the 3rd Quartile from the 1st Quartile. This will give us an Interquartile Range (IQR). The IQR gives us a statistical way to identify where the bulk of the statistical data points (the middle 50%) sit in the range, and how spread out that middle 50% is. Note that statistical data points include not only the values you see, but all the unseen numbers between the values.  In our example, the middle 50% spans 23.5 statistical data points.

.

Using IQR to Find Outliers

Now, the question is how far from the middle 50% can a value sit and still be considered a “reasonable” value? Leveraging the IQR, you can establish a “fence” by extending the “reasonable” range using these calculations:

1st Quartile – IQR

3rd Quartile + IQR

As you can see in our example, these calculations point to a new set of values which we call our Lower Fence (20.2) and our Upper Fence (90). Any values outside the fences are theoretically outliers.

.

That being said, applying fences based on the raw IQR would aggressively tag too many values as outliers. So Tukey established a 1.5 basis, stating that the IQR should be multiplied by 1.5.

.

This expands the “reasonable” range and reduces the amount of outliers to a more appropriate set of values. With Tukey’s method, outliers are:

values below (Quartile 1) – (1.5 × IQR)

values above (Quartile 3) + (1.5 × IQR)

.

In our example, we see that 5, 6, 7, 104 and 132 are statistical outliers.

.

There doesn’t seem to be any statistically-driven reason Tukey uses 1.5 as a hard basis for his method. In fact, if you wanted to be more conservative, you could use 3 x IQR to identify the “extreme” outliers.

Extreme outliers are :

values below (Quartile 1) – (3 × IQR)

values above (Quartile 3) + (3 × IQR)

.

.

Using Tukey’s Method in Excel

Tukey’s method is easy enough to apply in Excel. Calculate the needed values…

.

…then use those values in an IF statement to tag the outliers.

IF the value is less than the Lower Fence OR greater than the Upper Fence, then tag it as an outlier.

.

You can even get fancy and apply conditional formatting to highlight the outliers.

Simply create a rule that colors Cell Values not between the Lower Fence and Upper Fence.

.

.

.

Once you identify outliers, what do you do – delete them? mmm….No.

It’s typically not good to simply delete any values in a legitimate data set.

Outliers could be a result of data entry error, changes in business rules, or some other random variation of factors. Instead of removing them, it’s best practice is to investigate why they exist, where they came from, and what they mean to the analysis.

.

Wow….I just remembered that I used to be cool.

23 thoughts on “Highlighting Outliers in your Data with the Tukey Method

  1. damich

    Nice job explaining the statistics. I may have enjoyed statistics class if it was more like this. I hope you do more of them.

    I also hope Mrs. Pig doesn’t read these posts, unless she’s into that whole geek thing. I’d say that I hope your kids don’t read them, but they already know how uncool you are.

  2. AlexJ

    You ARE cool. And rather than using a retainer, you should be ON retainer. Thanks for a very good piece.

  3. Rick Grantham

    Interesting read. As I was going through it, I was wondering… Why the he** are they multiplying the IQR times 1.5? Whats the point?

    Was glad to read your note… “There doesn’t seem to be any statistically-driven reason Tukey uses 1.5 as a hard basis for his method. In fact, if you wanted to be more conservative, you could use 3 x IQR to identify the “extreme” outliers.”

    In my mind, Six Sigma and statistically driven analysis are the next steps in the evolution of BI (and hopefully Excel). I would typically need to use MiniTab or a similar program to perform this type of analysis. Thanks for providing some shortcuts here. Its appreciated.

    Next… if you could dumb down multi-variate regression or ANOVA in Excel, I would appreciate it :)

    Rick Grantham

  4. Robert

    Interesting article. I typically use the ZScore, or standard score, to identify outliers. I will see how this stacks up.

    I use a UDF to simplify this process of identifying outliers based on the ZScore, and as such, have made a UDF based on your article. The UDF is as follows:

    Option Explicit

    Function Tukey(Rng As Range, Rng2 As Range) As String
    Dim Quart1 As Double
    Dim Quart3 As Double
    Dim Lower As Double
    Dim Upper As Double
    Dim Interquartile As Double
    Quart1 = Application.WorksheetFunction.Quartile_Exc(Rng2, 1)
    Quart3 = Application.WorksheetFunction.Quartile_Exc(Rng2, 3)
    Interquartile = Quart3 – Quart1
    Lower = Quart1 – (Interquartile * 1.5)
    Upper = Quart3 + (Interquartile * 1.5)
    If Rng Upper Then Tukey = “Outlier”
    End Function

    Using the UDF above, the syntax to see if cell B4 is an outlier would be as follows:

    =Tukey(B4,$B$3:$B$22)

    If an outlier, it will say “Outlier” in the appropriate cell, otherwise, it will be blank.

  5. Doris Choo

    Thanks for interesting forecasting lesson 101. Most of all Mike’s great sense of humor comes thru these very dry stuff, making it fun and enjoyable.

  6. Rick

    Great article. I’m thinking about using it to determine outliers in student grades, but assuming the data is correct, is there even such a thing?
    Also, second time this week I saw the use of the OR() function. Great UDF Robert.

  7. Jeff

    In my range the lower fense was a negative number. Makes me question the validity of this method. The 1.5 multipler seems to be the issue.

    Now my range was 15 to 1500 and there were only 80 data points. Looking at the data you can see 15 stands out as an outlier but this negative lower fence suggests otherwise.

  8. datapig Post author

    Jeff:
    Your lower fence can absolutely be a negative number. If range is 15 to 1500, there are not only 80 data points. You see 80 numbers, but there are actually 1,485 statistical data points (every number from 15 to 1500) .

    Remember Statistics involves the numbers between the ones you actually see as well.

    Your lower and upper fences depends on the skew of your data. So the lower fence could absolutely be a negative.

  9. Sandeep Singh

    One question : Do we need to arrange the data in ascending or descending order befoer applying the above method ?

  10. Chris

    Just like Jeff above, using my data set on corn yields my lower fence was a negative number; yet when looking at the data it seemed clear that there were outliers skewing my data at the lower end. Also, a negative number isn’t possible in my real word. You can’t harvest a negative number of bags of corn from an acre; the least possible is ‘0’.

  11. Melissa B

    This was so incredibly helpful! I’m working on homework for a statistics class and could not wrap my head around how to find the outliers, but now I can :)

  12. J Miller

    I am attempting to use this concept on some data but my lower fence is resulting in a negative number. I checked the formula and have everything correct. Any idea what direction I should take based on this result? I really need an efficient method of identify outliers in this massive data set. One thing I had to do was put my data into a pivot table and then referenced the cell range of the pivot table to find the quartiles. Not sure if that is causing any issues. I organized the data so that the data points did not aggregate together but are individual data points.

  13. datapig Post author

    The lower fence can absolutely be a negative number. If your dataset contians many data points that are negative, this should be expected.

    It just means your lower fence outliers are those that skew more negative than your dataset sample.

  14. J Miller

    Well I actually have NO negative values in my data, so the fact that the lower fence is negative doesn’t help to remove outliers below that number.

  15. datapig Post author

    Jim: Then I would check to see if you didn’t transpose the Interquartile range calculations.

    IQR = (Quartile 3) – (Quartile 1)

    Lower Fence = (Quartile 1) – (1.5 × IQR)
    Upper Fence = (Quartile 3) + (1.5 × IQR)

  16. J Miller

    I thought the same thing and checked the formula more than once. My Q1 is .21 my Q3 is .65 and my IQR is .44. So my Upper Fence = 1.3 and my Lower Fence = -.4

    Any other thoughts on how to remove outliers?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>