Statistical
Median Absolute Deviation (MAD) & Iglewicz-Hoaglin Modified Z-Score outlier formula
Calculate median absolute deviation and a modified Z-score, including the zero-MAD case, to flag observations for investigation.
Compare two measures of distance from the center
For [10,12,14,16,100], the median is 14 and absolute deviations are [4,2,0,2,86], giving MAD=2. The mean is 30.4, and using sample standard deviation gives a conventional score near 1.79 for 100. A small sample can limit the usefulness of conventional Z-score thresholds.
The modified score uses 0.6745*(value-median)/MAD. For 100 it is 29.0035. Absolute scores above 3.5 can flag potential outliers for investigation; they do not prove a measurement is wrong or justify automatic deletion. Distribution, sample size and context matter.
This fixed example requires five numeric values and intact absolute-deviation helper formulas. Missing observations are rejected. With repeated values MAD can be zero even when some observations differ; the modified score is then undefined.
| Observation X (A) | |X - Median(14)| (B) | Standard Z-Score | Modified Z-Score (MAD = 2) |
|---|---|---|---|
| 10 | 4 | -0.52 | -1.3490 (not flagged) |
| 12 | 2 | -0.47 | -0.6745 (not flagged) |
| 14 (Median) | 0 | -0.42 | 0.0000 (Center) |
| 16 | 2 | -0.37 | 0.6745 (not flagged) |
| 100 | 86 | 1.79 | 29.0035 (potential outlier) |
Median Absolute Deviation MAD = MEDIAN(B2:B6)
B2:B6 computes =ABS(A2 - MEDIAN($A$2:$A$6)) for each observation.
=IF(COUNT(A2:A6)<>5,"Invalid inputs",MEDIAN(B2:B6))Iglewicz-Hoaglin Modified Z-Score (> 3.5 outlier test)
0.6745 approximates the standard normal 75th percentile. Dividing MAD by it provides a normal-reference scale estimate, not an exact standard deviation for arbitrary data. Use the unrounded score for threshold decisions; ROUND here is only for display.
=IF(COUNT(A2:A6)<>5,"Invalid inputs",IF(MEDIAN(B2:B6)=0,"Undefined (MAD = 0)",ROUND(0.6745 * (A6 - MEDIAN($A$2:$A$6)) / MEDIAN(B2:B6), 4)))Try the example
Use the original values or change them to test your understanding.