Descriptive statistics
STDEV.S vs STDEV.P: measure sample variation vs full population spread
Know when to divide squared deviations by n - 1 (STDEV.S for quality samples) or n (STDEV.P for complete populations), and why a single reading returns #DIV/0!.
Four bag weights pulled from a production run
A coffee roaster weighs four sample bags from a morning batch: 10, 12, 14, and 16 ounces (A2:A5). Their mean is 13, and the squared deviations from 13 sum to (-3)^2 + (-1)^2 + 1^2 + 3^2 = 20.
| Function | Divisor | Variance | Standard deviation |
|---|---|---|---|
| STDEV.S(A2:A5) | n - 1 = 3 | 20 / 3 = 6.6667 | 2.5820 |
| STDEV.P(A2:A5) | n = 4 | 20 / 4 = 5.0000 | 2.2361 |
Use STDEV.S for samples and STDEV.P for complete censuses
Because four bags are only a sample of the whole day’s production, =ROUND(STDEV.S(A2:A5), 4) divides by n - 1 (3) and returns 2.582. Use STDEV.P (2.2361) only when A2:A5 contains every single item in the entire group being described.
=ROUND(STDEV.S(A2:A5),4)Equal values (0) and the single-observation #DIV/0! boundary
When all readings are identical (for example four 13s), both functions return 0. With only one numeric value (n = 1), n - 1 is zero, so STDEV.S(A2) returns #DIV/0! whereas STDEV.P(A2) returns 0.
=ROUND(STDEV.P(A2:A5),4)Try the example
Use the original values or change them to test your understanding.