Flag values more than two standard deviations out
A simple, defensible outlier rule built from AVERAGE and STDEV.
Task:
Before averaging these meter readings you want to flag anything unusual. In C2:C10 show Check for any reading more than two standard deviations from the mean of B2:B10, and Normal for the rest.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of STDEV, VAR
0/3The same formula in the other shapes it takes at work.
- STDEV Function
The same spread as VAR, back in units you can read.
CoreAdvanced - Coefficient of variation: spread relative to size
STDEV divided by AVERAGE lets you compare things measured on different scales.
Advanced - Flag values more than two standard deviations out
A simple, defensible outlier rule built from AVERAGE and STDEV.
Advanced
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | |
|---|---|---|---|
| 1 | Reading | kWh | Flag |
| 2 | R1 | 52 | |
| 3 | R2 | 49 | |
| 4 | R3 | 55 | |
| 5 | R4 | 51 | |
| 6 | R5 | 120 | |
| 7 | R6 | 48 | |
| 8 | R7 | 53 | |
| 9 | R8 | 50 | |
| 10 | R9 | 54 |
What this exercise teachesMay contain the answer
Two standard deviations is a common, explainable cut-off: in roughly normal data only about 1 reading in 20 falls outside it. R5 at 120 is far outside, and it also inflates the mean and standard deviation it is measured against — which is why outliers are flagged before the summary statistics are reported, not after.