Statistical
VAR.S vs VAR.P: calculate sample variance (n - 1) vs population variance (n)
Choose VAR.S when analyzing a sample of observations and VAR.P when you have every member of a closed population.
Why sample variance divides by n - 1 instead of n
Variance averages squared deviations from the mean—the building block of standard deviation (SQRT(VAR.S(...)) = STDEV.S(...)) and portfolio risk.
VAR.S(number1, ...) divides squared deviations by n - 1 (Bessel's correction) for an unbiased estimate from a sample, whereas VAR.P divides by n when your dataset is the entire population.
| Run (A) | CycleSec (B) | Deviation (x - 21) | Squared |
|---|---|---|---|
| R1 | 18 | -3 | 9 |
| R2 | 20 | -1 | 1 |
| R3 | 22 | +1 | 1 |
| R4 | 24 | +3 | 9 |
Compare 20 / 3 (VAR.S) vs 20 / 4 (VAR.P)
With mean = 21 and sum of squares = 20 across n = 4 runs, VAR.S returns 20 / 3 = 6.6667 while VAR.P returns 20 / 4 = 5.
=ROUND(VAR.S(B2:B5), 4)Single-observation boundary (#DIV/0! vs 0)
On a single data point (n = 1), n - 1 = 0 so VAR.S(B2:B2) returns #DIV/0!, whereas VAR.P(B2:B2) returns 0.
=VAR.S(B2:B2)Try the example
Use the original values or change them to test your understanding.