STDEV vs STDEVP: Sample or Population Standard Deviation

Ten cartons come off a filling line and get weighed: 16.1, 15.8, 16.3, 16.0, 15.9, 16.2, 16.1, 15.7, 16.4, 16.0 ounces. The control rule for the line says a batch is out of control when the fill-weight standard deviation climbs above 0.21 oz. Type =STDEV.S(A2:A11) and you get 0.2173, so the batch is held. Type =STDEVP(A2:A11) on the same ten cells and you get 0.2062, so it ships. Nothing changed except the denominator: 9 instead of 10. The 0.0112 oz between those two answers is smaller than the scale's own resolution, and it is the entire distance between a release and a hold. The mean is 16.05 oz either way, the spec window runs 15.7 to 16.3, and one carton at 16.4 is already outside it. Standard deviation never flagged that carton. It cannot. It describes how the ten readings cluster, not whether any single one complies.

STDEV.S divides by n-1 because one degree of freedom went into estimating the mean from the same data. STDEVP divides by n because nothing was estimated. That is the whole theory, and it is easier to apply backwards from the decision: does a larger group exist that these ten cartons stand for? Ten cartons pulled from a 2,000-carton run stand for the run, so the answer is 0.2173. If those ten are the whole batch, the answer is 0.2062, because there is no larger group to infer anything about. Two sentences of context settle the formula, and neither sentence lives in the spreadsheet. "We have the whole month" is the line that gets this wrong most often. A month of daily readings is a sample of the process, not a population, unless the question is literally about those 30 days and nothing beyond them. The same reasoning covers a year of sales, one shift of output, and a quarter of returns: time-boxed data is nearly always a sample. A complete count of a defined set is a population, such as every carton in this batch, every employee on the payroll file for that date, or all 50 states.

The two functions are tied together by a fixed multiplier, SQRT(n/(n-1)), which depends on nothing but the count. At n=10 the sample figure runs 5.41% higher. At n=5 it is 11.8% higher, at n=30 1.71%, at n=100 0.50%, and at n=1,000 0.05%. At n=2 the sample figure is 41.4% higher, which is a fair warning about two-point studies. Microsoft's own documentation for STDEV.S uses ten breaking-strength readings from 1299 to 1370 and reports 27.46391572. STDEVP on that same list returns 26.05455814. Round both to whole pounds and the answers are 27 and 26, so the divisor is visible even at the precision most reports use. The decision only gets harder as n grows: on a hundred points the two figures differ by half a percent, which will not change the shape of a chart and can still cross a threshold written to two decimals.

Replace A7 with the text n/a and clear A11, and STDEV.S returns 0.2449 instead of 0.2173. The count fell from ten to eight, so the divisor moved from 9 to 7, while the mean of the eight remaining weights still lands on 16.05. COUNT reports 8 and COUNTA reports 9, which is the quickest way to see what the function is really reading. Text inside a referenced range is skipped. Text typed straight into the formula is not skipped, it is an error: =STDEV.S(16.1,"n/a") returns #VALUE!, and the same goes for any argument that cannot be translated into a number. Logical values follow the same split, ignored inside a range and counted as 1 or 0 when typed directly into the argument list. STDEVA and STDEVPA reverse all of this by counting text as zero. On this column that turns 0.2449 into 5.3549, a figure describing a 0 oz carton nobody produced. A single numeric value is the other trap: =STDEV.S(B2) returns #DIV/0! because the divisor is zero, while =STDEVP(B2) returns 0 and looks perfectly healthy.

STDEV.S and STDEV.P arrived with Excel 2010. Open a workbook built on them in Excel 2007, or in a viewer that only knows the older function table, and the formulas return #NAME? with an _xlfn prefix in the formula bar. That is the only real cost of the dotted names, and it is a version problem rather than a math problem. Google Sheets lists both spellings: its function list maps STDEV.S to STDEV and STDEV.P to STDEVP, so either one evaluates and both match Excel to the last digit, 0.2173 on this column from either app. STDEVA and STDEVPA have no dotted versions anywhere, so the text-as-zero variants only exist under their original names. In new work, type the dotted names. The divisor is right there in the formula, and a reviewer reading =STDEV.S(A2:A11) knows exactly what claim the number makes without opening a methods note. Keep STDEV and STDEVP where a file has to survive Excel 2007, or where an export pipeline parses formula text and only recognizes the legacy table. The trade-off is real but small: longer formulas in exchange for a self-documenting one.