Statistical Analysis
Population Variance & Standard Deviation (VAR.P & STDEV.P vs. Bessel N-1 Correction)
Choose accurately between N-denominator population dispersion (VAR.P, STDEV.P) for complete censuses and N-1 sample estimators (VAR.S, STDEV.S), and verify the exact N/(N-1) Bessel scaling ratio.
When to divide by N (census) versus N - 1 (sample)
When your worksheet contains every member of a closed population—such as all 5 regional plants in a company or every settled trade in a daily book—the population mean is known without sampling error, so population variance VAR.P divides the sum of squared deviations by N and STDEV.P takes its square root.
When those rows are only a sample drawn from a wider population, centering around the sample mean slightly understates dispersion; Bessel's correction replaces N with N - 1 in VAR.S and STDEV.S so the variance estimator remains unbiased, inflating VAR.P by exactly N / (N - 1).
| Function Pair | Denominator | Use Case | Result on [10, 12, 14, 16, 18] |
|---|---|---|---|
| VAR.P / VARP | N (5) | Complete census of a finite group | 8.0000 |
| VAR.S / VAR | N - 1 (4) | Unbiased sample variance estimate | 10.0000 |
| STDEV.P / STDEVP | SQRT(N) | Population root-mean-square spread | 2.8284 |
| STDEV.S / STDEV | SQRT(N - 1) | Sample standard deviation | 3.1623 |
Population Variance & Standard Deviation Formulas
Use VAR.P and STDEV.P (or legacy aliases VARP and STDEVP) to measure exact dispersion across all numeric entries while ignoring empty cells.
=ROUND(STDEV.P(B2:B6), 4)Exact Bessel Variance Scaling Audit
You can verify at any sample size N >= 2 that VAR.S(range) / VAR.P(range) equals COUNT(range) / (COUNT(range) - 1).
=ROUND(VAR.S(B2:B6) / VAR.P(B2:B6), 4)Try the example
Use the original values or change them to test your understanding.