The IQR rule (Tukey's fences) flags a value as an outlier if it lies more than 1.5 interquartile ranges below the first quartile or above the third quartile.
Outlier if value < Q1 − 1.5 × IQR or value > Q3 + 1.5 × IQR
On the 30-day sample below: 1.
When to use it
It is robust: the fences come from the middle half of the data, so the outliers cannot widen them, unlike a rule based on the standard deviation.
Worked example
On the 30 days the rule flags exactly one day, day 17, the one-day sale. That is the point of the check: it finds the days whose cause you should know before averaging them in.
=COUNTIF(C2:C31,">"&QUARTILE.INC(C2:C31,3)+1.5*(QUARTILE.INC(C2:C31,3)-QUARTILE.INC(C2:C31,1)))+COUNTIF(C2:C31,"<"&QUARTILE.INC(C2:C31,1)-1.5*(QUARTILE.INC(C2:C31,3)-QUARTILE.INC(C2:C31,1)))| Day | Website orders |
|---|---|
| 1 | 24 |
| 2 | 27 |
| 3 | 22 |
| 4 | 30 |
| 5 | 28 |
| 6 | 25 |
| 7 | 31 |
| 8 | 29 |
| 9 | 26 |
| 10 | 33 |
| 11 | 30 |
| 12 | 28 |
| 13 | 35 |
| 14 | 32 |
| 15 | 29 |
| 16 | 34 |
| 17 | 61 |
| 18 | 31 |
| 19 | 36 |
| 20 | 33 |
| 21 | 30 |
| 22 | 37 |
| 23 | 35 |
| 24 | 32 |
| 25 | 38 |
| 26 | 36 |
| 27 | 34 |
| 28 | 40 |
| 29 | 37 |
| 30 | 39 |
Common mistakes
- An outlier is not an error. Investigate it before removing it; here it is a real sale.
- Data with a long tail (order values, for example) has many "outliers" by this rule. Consider a log scale or a 3 × IQR fence.
- Trends inflate the IQR. On a growing series, apply the rule to the change from a trend, not the raw values.
Calculate it yourself
Related methods
See all statistics methods or the business metrics they are used on.
Guides that use it
- How to find out what changed in your sales data — 2 min read
- Where to find free public data, and how to check it before you trust it — 2 min read
- How to clean a messy CSV before you analyse it — 2 min read
- How to do data analysis: 6 steps with a worked example — 4 min read
- Data analysis methods and techniques, and when to use each — 3 min read
- Python and SQL for data analysis, side by side — 3 min read
- Data analysis examples: 6 worked from one dataset — 3 min read
- Free online data analysis tools, and how to choose one — 3 min read
Further reading
Questions people ask
- What is finding outliers with the IQR rule?
- The IQR rule (Tukey's fences) flags a value as an outlier if it lies more than 1.5 interquartile ranges below the first quartile or above the third quartile.
- What is the formula for finding outliers with the IQR rule?
- Outlier if value < Q1 − 1.5 × IQR or value > Q3 + 1.5 × IQR. On the 30-day sample data the result is 1.
- How do you calculate finding outliers with the IQR rule in Excel?
- Use =COUNTIF(C2:C31,">"&QUARTILE.INC(C2:C31,3)+1.5*(QUARTILE.INC(C2:C31,3)-QUARTILE.INC(C2:C31,1)))+COUNTIF(C2:C31,"<"&QUARTILE.INC(C2:C31,1)-1.5*(QUARTILE.INC(C2:C31,3)-QUARTILE.INC(C2:C31,1))) with the data in rows 2 to 31. The same formula works in Google Sheets.
About the data on this page
The figures come from a small made-up dataset, printed above in full so you can check every number by hand. Download it as daily.csv. The results are computed from those rows when the page is built, and the Excel formulas, SQL and Python on these pages are run against the same rows before publishing, so the code and the answers agree.
To get these numbers from your own data without writing formulas, try AnalyzeIt: ask in plain words and it shows the working behind every figure.
Legal entity
Devansh Yadav
Moradabad, Uttar Pradesh
Governing law: Courts at Moradabad, Uttar Pradesh
Phone: +91 8433475698
Legal: legal@analyzeit.in