Statistical
Calculate Grubbs G and an upper-end Dixon gap ratio
Calculate two single-extreme statistics for six numeric observations, with explicit assumptions and no automatic deletion of data.
Compute statistics before interpreting an unusual observation
Grubbs G measures the largest absolute deviation from the mean relative to sample standard deviation. A single-outlier test assumes approximately normal observations. Multiple unusual values can mask each other; repeatedly deleting values and rerunning a single-outlier test is not a general multiple-outlier procedure.
The Dixon expression shown is the upper-end gap divided by full range for this six-value example. Other sample sizes and test variants can use different expressions and critical values. It does not test the lower endpoint.
This worksheet is an arithmetic exercise, not a laboratory compliance rule or authorization to discard a result. Review measurement provenance, sampling assumptions and the applicable procedure. A threshold comparison is not an exact p-value, and no threshold exceedance does not certify that every observation is valid.
Require six numeric observations. Equal observations produce an undefined statistic because both spread denominators are zero. D2 is a supplied rounded Grubbs comparison threshold for this fixed example; a production test must compute or verify the threshold for its actual sample size, tail convention and significance level.
| Outlier Test Metric | Formula (rng = A2:A7) | Contaminated [10..14, 30] | Clean [10..15] |
|---|---|---|---|
| Sample Mean & STDEV.S | AVERAGE & STDEV.S | Mean = 15.00, s = 7.4833 | Mean = 12.50, s = 1.8708 |
| Grubbs Statistic G | MAX(A-Mean, Mean-Min) / s | 15 / 7.4833 = 2.0045 | 2.5 / 1.8708 = 1.3363 |
| Dixon Upper Gap Ratio Q | (LARGE(1) - LARGE(2)) / Range | (30 - 14) / (30 - 10) = 0.80 | (15 - 14) / (15 - 10) = 0.20 |
| Threshold comparison only | Compare unrounded G with supplied D2 | Above supplied threshold | At or below supplied threshold |
Two-sided Grubbs Outlier Statistic G = Max(|Max - Mean|, |Mean - Min|) / STDEV.S
Using MAX(MAX(rng) - AVERAGE(rng), AVERAGE(rng) - MIN(rng)) avoids array coercion while testing both upper and lower extremes.
=IF(COUNT(A2:A7)<>6,"Invalid inputs",IF(MAX(A2:A7)=MIN(A2:A7),"Undefined spread",ROUND(MAX(MAX(A2:A7) - AVERAGE(A2:A7), AVERAGE(A2:A7) - MIN(A2:A7)) / STDEV.S(A2:A7), 4)))Dixon's Q-Test Ratio for an upper suspect replicate
Divides the gap between the largest and second-largest replicate by total sample range.
=IF(COUNT(A2:A7)<>6,"Invalid inputs",IF(MAX(A2:A7)=MIN(A2:A7),"Undefined spread",ROUND((LARGE(A2:A7, 1) - LARGE(A2:A7, 2)) / (MAX(A2:A7) - MIN(A2:A7)), 4)))Compare G with a supplied threshold
Compare the unrounded statistic. This does not calculate a p-value, test normality, or remove any observation.
=IF(COUNT(A2:A7)<>6,"Invalid inputs",IF(MAX(A2:A7)=MIN(A2:A7),"Undefined spread",IF(COUNT(D2)<>1,"Invalid inputs",IF(D2<=0,"Invalid inputs",IF((MAX(MAX(A2:A7) - AVERAGE(A2:A7), AVERAGE(A2:A7) - MIN(A2:A7)) / STDEV.S(A2:A7)) > D2, "Above supplied threshold", "At or below supplied threshold")))))Try the example
Use the original values or change them to test your understanding.