Level 4 · STDEV.S or STDEV.P?

Excel has two standard deviation functions and they give different answers on the same cells. The difference is small here, under 2%, but the question behind it comes up in every interview and every review: are these numbers the whole story, or a sample of something bigger?

Two formulas, one difference

Both functions measure the typical distance from the mean. They square each day’s distance from the average, add the squares up, divide, and take the square root. The only difference is what they divide by:

Function	Divides by	Outdoor, January 2025
=STDEV.P(C2:C32)	n = 31	1,182.52
=STDEV.S(C2:C32)	n − 1 = 30	1,202.07

Dividing by a smaller number gives a larger result. The ratio is always √(n ÷ (n − 1)); with 31 values that is about 1.0165, and 1,182.52 × 1.0165 is 1,202.07.

Why n − 1 for a sample

The spread is measured around the sample’s own average, $694.21. That average was computed from these same 31 days, so it sits a little closer to them than the true long-run average of Outdoor’s January days would. The squared distances come out slightly too small. Dividing by n − 1 instead of n corrects for that. With 5 values the correction is big (√(5/4), about 12%); with 1,000 values it is negligible.

Sample or population?

Ask what the number will be used for:

• Population (STDEV.P): the cells are the entire group and you only describe it. The ages of everyone on one team, the scores in one finished class, the prices of the 20 products in a category today.
• Sample (STDEV.S): the cells stand in for something larger. One month to plan the next, a survey of 400 customers out of 2,000, last year’s days to predict next year’s.

Analysts nearly always predict, compare or generalize, so STDEV.S is the default. STDEV is the older name for the same calculation, kept for old workbooks. The same split exists for variance: VAR.S and VAR.P.

Common mistakes

• “It’s every row I have, so it’s the population.” Having every row of a sheet doesn’t make it the whole group you care about.
• Treating the gap as an error. The two functions differ by design, and the gap shrinks as the data grows.
• Mixing them in one report. Pick one per analysis and say which, so numbers compare.