Click here to Skip to main content
11,580,572 members (67,623 online)
Click here to Skip to main content

Statistical Outliers detection: worksheet solution

, 16 Feb 2015 CPOL 38.8K 3
Rate this:
Please Sign up or sign in to vote.
Statistical Outliers detection in Microsoft Excel worksheet using Median() and array formula

Theoretical background

"Outliers" are defined as numeric values in any random data set, which have an unusually high deviation from either the statistical mean (average) or the median value. In other words, these numbers are either relatively very small or too big. Detecting the outliers in a data set represents a complex statistical problem, with a corresponding variety of different methodologies and computational techniques as described, for example, in the NIST publication [1]. In general, finding the "Outliers" in a data set could be done by calculating the deviation for each number, expressed as either a "Z-score" or "modified Z-score" and testing it against certain predefined threshold. Z-score typically refers to number of standard deviation relative to the statistical average (in other words, it's measured in "Sigmas"). Modified Z-score applies the median computation technique to measure the deviation and in many cases provides more robust statistical detection of outliers. Mathematically the Modified Z-score could be written (as suggested by Iglewicz and Hoaglin [1]) as:

Mi=0.6745 * (Xi -Median(Xi)) / MAD,

where MAD stands for Median Absolute Deviation. Any number in a data set with the absolute value of modified Z-score exceeding 3.5 is considered an "Outlier". Modified Z-score could be used to detect outliers in Microsoft Excel worksheet as described below.

Sample computation of outliers in Excel worksheet using Media/MAD

Step 1. Open Microsoft Excel worksheet and enter a sample set of 10 randomly selected numbers in column A, starting with the first row: 3, 1, -23, 7, 0, 12, -2, 7, 2, 1 (note: don’t enter commas)
 
Step 2. In the first row of column C (in other words, C1) enter the formula:

=MEDIAN(A1:A10)

The value in this cell corresponds to the median calculated on a data set entered at step 1.
 
Step 3. In the second row of column C (in other words, C2) enter the array formula:

{=MEDIAN(ABS(MEDIAN(A1:A10)-A1:A10))}

The value in this cell (C2) corresponds to MAD [1].
 
Step 4. Enter the formula:

=IF((ABS(C$1-A1)>3.5*C$2), "OUTLIER", "NORMAL")

in the first row of column B and extend it down to the 10th row. Final result of “outlier’s detection” should appear in column B, indicating two "outliers" numbers (-23 and 12) as shown below:
 

3 NORMAL
1 NORMAL
-23 OUTLIER
7 NORMAL
0 NORMAL
12 OUTLIER
-2 NORMAL
7 NORMAL
2 NORMAL
1 NORMAL

Sample computation of outliers using STD/Mean values

The first method described above in based on Modified Z-score and underlying Median/MAD computation. In many cases (particularly for small sample sizes) it provides more robust statistical outliers detection than a "traditional" z-score, which implies the detection based on Standard Deviation and Mean (average). Just for reference purposes, the sample computation below describes the outliers detection based on "2-Sigma" z-score; in other words, any numeric value in a data set considered an "outlier" if its absolute value of deviation from the statistical average exceeds 2*STD. Note: as mentioned above, the first method is typically more robust and recommended for small sample sizes:
 
Step 1. Use the same Excel Worksheet with sample set of 10 sample numbers in column A as described in previous example.
 
Step 2. Enter the formula: =AVERAGE(A1:A10) into the cell F1. This corresponds to the statistical mean calculated on a data set in column A.
 
Step 3. enter the formula: =STDEV(A1:A10) into the cell F2. This corresponds to the Standard Deviation.
 
Step 4. Enter the formula: =IF(ABS(A1-F$1)>2*F$2,"OUTLIER", "NORMAL")in the first row of column E and extend it down to the 10th row. Final result of the “outlier’s detection” using statistical STD/AVERAGE on z-score level of 2 should appear in column E, indicating only one "outlier" number: -23 (see below); this method is missing a second outliers, which is 12, as found in the previous method.

NORMAL
NORMAL
OUTLIER
NORMAL
NORMAL
NORMAL
NORMAL
NORMAL
NORMAL
NORMAL

Notes

Both methods of statistical detection of Outliers discussed above could be easily implemented within .NET framework. Suggested MS Excel implementation based upon worksheet functions was chosen mostly to serve the didactic/demo purposes, providing the maximum level of simplicity and clarity. For more details on statistical outliers detection methodology and computation technique you could refer to the reference [1]. Also, various statistical calculations could be performed online by using free Statistics Calculator [2], which is actually implemented as ASP.NET/C# solution.

References

  1. Detection of Outliers
  2. Online Statistical Calculator

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

DrABELL
President Infosoft International Inc
United States United States
Dr. A. Bell has 20+ years of Software and Electrical Engineering experience: Win/Web veteran, published 300+ articles and authored 37 inventions, credited for 10+ Enterprise level projects (>250k code lines); currently focused on .NET/WPF, C#, HTML5, jQuery, SQL, 'Big Data', AI, Speech Tech and Mobile apps. He participated in App Innovation Contest (AIC 2102/2013) with several winning submissions. Sample projects/pubs follow:
  1. Edumatter M12: School Math Calculators and Equation Solvers (contest winner)
  2. Engineering Calculator VOLTA-2013 (contest winner)
  3. WebTV Project: Embedded YouTube Player (Goog #1 YouTube API for ASP.NET)
  4. Online 3 Fractions Calculator (#1 on Goog)
  5. Top-100 Digital Cameras (powered by iMark-DCAM rating engine)
  6. Engineering Calculator VOLTA-814 for Windows
  7. Pericles™ TTS-14 Text-to-Speech Synthesizer (Win)
  8. Semaphon™ SP-300 semantic phone number converter (Win)
  9. PaydayNY-2015 Payroll Tax Calculator (Win)
  10. 'Find Job Agency' Online Interactive Map (Microsoft Bing technology)

You may also be interested in...

Comments and Discussions

 
QuestionSmall error Pin
walobu18-Feb-15 14:37
memberwalobu18-Feb-15 14:37 
GeneralRe: Thanks a bunch, ASP-DOT-NET-DEV-U-THE-BEST! :) Actually, I t... Pin
DrABELL30-Jun-11 13:37
memberDrABELL30-Jun-11 13:37 
GeneralReason for my vote of 5 A robust, simple and efficient metho... Pin
Alex Tuma28-Jun-11 9:22
memberAlex Tuma28-Jun-11 9:22 
GeneralRe: Hi Alex, You are absolutely right: this very robust yet simp... Pin
DrABELL28-Jun-11 14:03
memberDrABELL28-Jun-11 14:03 
GeneralHi Walt, Thanks for your note. Let's me clarify couple thing... Pin
DrABELL28-Jun-11 3:20
memberDrABELL28-Jun-11 3:20 
GeneralIt appears that you are assuming a normal distribution, with... Pin
Walt Fair, Jr.25-Jun-11 16:22
subeditorWalt Fair, Jr.25-Jun-11 16:22 
GeneralRe: FYI, Walt, DrABELL posted a comment but did not post it as a... Pin
AspDotNetDev30-Jun-11 8:58
protectorAspDotNetDev30-Jun-11 8:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150603.1 | Last Updated 16 Feb 2015
Article Copyright 2011 by DrABELL
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid