Statistical
Detect Outliers (IQR Method)
Detect statistical outliers in your data using the IQR (Interquartile Range) method. Values below Q1-1.5×IQR or above Q3+1.5×IQR are flagged as outliers.
Excel Formula
=IF(OR(A2<QUARTILE($A$2:$A$100,1)-1.5*(QUARTILE($A$2:$A$100,3)-QUARTILE($A$2:$A$100,1)), A2>QUARTILE($A$2:$A$100,3)+1.5*(QUARTILE($A$2:$A$100,3)-QUARTILE($A$2:$A$100,1))), "Outlier", "Normal")Step-by-Step Explanation
1
QUARTILE(data,1) calculates Q1 (25th percentile)
2
QUARTILE(data,3) calculates Q3 (75th percentile)
3
Q3-Q1 gives the IQR (Interquartile Range)
4
Lower bound = Q1 - 1.5 × IQR
5
Upper bound = Q3 + 1.5 × IQR
6
Values outside these bounds are outliers
Example
| Value (A) | Status (B) |
|---|---|
| 25 | Normal |
| 28 | Normal |
| 150 | Outlier |
| 30 | Normal |
| 2 | Outlier |
Result: Values far from the middle range are flagged as Outlier
Common Variations
Z-score method
=IF(ABS((A2-AVERAGE($A:$A))/STDEV($A:$A))>2,"Outlier","Normal")Flag if >2 standard deviations
Lower fence only
=QUARTILE(A:A,1)-1.5*(QUARTILE(A:A,3)-QUARTILE(A:A,1))Calculate lower bound
Upper fence only
=QUARTILE(A:A,3)+1.5*(QUARTILE(A:A,3)-QUARTILE(A:A,1))Calculate upper bound
Need a Custom Version?
Use our AI generator to create a formula tailored to your specific data and requirements.
Try It with AI →