Catch a batch drifting out of tolerance
The average can sit dead on target while the batch itself is out of control. STDEV is what actually says so.
Task:
You do quality control on a machine shop floor. Six bolts were pulled from this morning's run and their diameters measured in mm — the target is 8.00mm, but the real question is how consistent the batch is, not whether the average happens to land near it. Work out the average diameter in B8, the standard deviation in B9, then in B11 report whether the process is "In control" or "Out of control" by comparing the standard deviation against the tolerance in B10.
Learning Objectives:
- Measure a batch's spread with STDEV rather than trusting AVERAGE alone
- Turn a standard deviation into a pass/fail with an ordinary IF
- Keep a tolerance as an editable input cell instead of a number buried in a formula
Solve without hints for +5 XP
Interactive Spreadsheet
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 | |
|---|---|---|
| 1 | Sample | Diameter (mm) |
| 2 | 1 | 7.95 |
| 3 | 2 | 8.06 |
| 4 | 3 | 7.93 |
| 5 | 4 | 8.08 |
| 6 | 5 | 7.97 |
| 7 | 6 | 8.04 |
| 8 | Average diameter | |
| 9 | Standard deviation | |
| 10 | Tolerance (max allowed stdev) | 0.03 |
| 11 | Process in control? |
What this exercise teaches (contains the answer)
AVERAGE(B2:B7) comes out at 8.005mm, close enough to target that a report built on the average alone would wave this batch through — the six diameters actually run from 7.93mm to 8.08mm, a 0.15mm spread that never shows up in a number built by summing and dividing. STDEV(B2:B7) is what exposes it: about 0.063mm of typical scatter around the mean, more than double the 0.03mm the process is supposed to hold to. IF(B9<=B10,...) is the ordinary threshold check, but reading the tolerance out of B10 rather than writing 0.03 straight into the formula is what lets someone tighten the spec next quarter by editing one cell instead of hunting through every formula that mentions it.