Statistical
Interquartile Range (IQR) formula and 1.5xIQR outlier detection fences
Measure middle-50% spread with QUARTILE.INC(r, 3) - QUARTILE.INC(r, 1) and flag potential outliers outside Tukey's Q1 - 1.5*IQR and Q3 + 1.5*IQR fences.
Use IQR fences to flag observations for investigation
A single extreme spike (like 50 in {10, 12, 14, 15, 16, 18, 50}) inflates both AVERAGE and STDEV.S—stretching standard-deviation thresholds wide enough to hide the very outlier you want to catch.
Interquartile Range IQR = Q3 - Q1 (17 - 13 = 4) depends strictly on the middle 50% of values. Tukey's standard box-plot fences sit at Lower = Q1 - 1.5*IQR (7) and Upper = Q3 + 1.5*IQR (23), immediately flagging 50 as an outlier.
| Metric | Formula | Value on {10,12,14,15,16,18,50} |
|---|---|---|
| Q1 (25th pct) | =QUARTILE.INC(A2:A8, 1) | 13 |
| Q3 (75th pct) | =QUARTILE.INC(A2:A8, 3) | 17 |
| IQR | =Q3 - Q1 | 4 |
| Upper Fence | =Q3 + 1.5 * IQR | 23 (flags 50) |
IQR, upper/lower fences, and outlier count formulas
Bind quartileLow, quartileHigh, and iqr with LET so COUNTIF totals both low and high outliers in one formula.
=QUARTILE.INC(A2:A8, 3) - QUARTILE.INC(A2:A8, 1)Count total outliers outside 1.5xIQR fences
Adds values strictly below Q1 - 1.5*IQR and strictly above Q3 + 1.5*IQR.
=LET(quartileLow, QUARTILE.INC(A2:A8, 1), quartileHigh, QUARTILE.INC(A2:A8, 3), iqr, quartileHigh - quartileLow, COUNTIF(A2:A8, "<" & (quartileLow - 1.5 * iqr)) + COUNTIF(A2:A8, ">" & (quartileHigh + 1.5 * iqr)))Inputs and interpretation
Fences flag possible outliers; they do not establish that an observation is erroneous. Quartile interpolation depends on the chosen INC convention and sample size. Use a nonempty numeric range. With IQR zero, any different value may be flagged; investigate the distribution rather than deleting rows automatically.
Try the example
Use the original values or change them to test your understanding.