How to Use the VAR.S and VAR.P Functions in Microsoft Excel to Calculate Variance

In quantitative finance, quality assurance, engineering, and data science, measuring central tendency with the mean or median only tells half the story. Two investment funds may both deliver an average annual return of 8%, yet one fund might oscillate wildy between −30% and +45%, while the other delivers steady returns between +6% and +10%. Understanding the dispersion and spread of data around that average requires calculating variance.

Variance measures the average squared distance of each individual data point from the arithmetic mean. In Microsoft Excel, calculating variance correctly requires choosing between two specialized functions based on your data collection scope: VAR.S (for sample datasets) and VAR.P (for entire populations). Selecting the wrong function introduces mathematical bias into standard deviation, confidence intervals, and hypothesis tests. This tutorial breaks down the mathematical distinction between sample and population variance, details Excel formula syntax, demonstrates practical business examples, and covers common data handling errors.

Sample Variance vs. Population Variance: Bessel’s Correction

The mathematical difference between VAR.S and VAR.P lies in the denominator used during calculation:

Function	Dataset Classification	Denominator	Formula
VAR.S(number1, ...)	Sample (A subset or representative sample of a larger group)	n − 1 (Bessel’s correction)	s2 = ∑(xi − x̄)2 / (n − 1)
VAR.P(number1, ...)	Population (The complete dataset containing all members)	N (Total observations)	σ2 = ∑(xi − μ)2 / N

Why does VAR.S divide by n − 1? In statistics, calculating variance from a sample tends to underestimate the true population variance because sample points cluster more tightly around the sample mean than around the true population mean. Dividing by n − 1 (known as Bessel’s correction) mathematically corrects this bias, providing an unbiased estimator.

1. Using the VAR.S Function (Sample Data)

Use VAR.S in virtually all practical analytical scenarios where you examine sample data (survey responses, test batches, medical trials, sample quality audits):

=VAR.S(number1, [number2], ...)

• number1 (Required): The first numeric argument, cell reference, or range corresponding to your sample.
• number2, … (Optional): Up to 254 additional arguments or ranges.

Practical Example: Product Batch Quality Control

A manufacturing plant produces 50,000 metal bolts per day. Quality engineers inspect a random sample of 10 bolts to measure thread diameter tolerances (in millimetres):

Data in cells A2:A11: 10.02, 9.98, 10.05, 10.01, 9.97, 10.03, 10.00, 9.99, 10.04, 9.96

=VAR.S(A2:A11)

Excel computes the sample variance as 0.001049 mm², properly adjusting for sample size degrees of freedom.

2. Using the VAR.P Function (Population Data)

Use VAR.P strictly when your spreadsheet contains the entire, complete population of data with no missing or unrecorded members:

=VAR.P(number1, [number2], ...)

Practical Example: Complete Corporate Headcount

A boutique consulting firm has exactly 15 employees. If you want to calculate the variance of employee ages across the entire company:

Data in cells C2:C16: Ages of all 15 staff members.

=VAR.P(C2:C16)

Because no employees were sampled or excluded, dividing by N (15) provides the exact population variance.

Legacy Functions vs. Modern Standards

In older spreadsheets created before Excel 2010, you may encounter legacy functions:

• VAR: The legacy equivalent of VAR.S. Still supported for backward compatibility, but Microsoft strongly recommends using VAR.S for clarity.
• VARP: The legacy equivalent of VAR.P. Replaced by VAR.P.
• VARA / VARPA: Evaluates logical values (TRUE as 1, FALSE as 0) and text strings as 0. Rarely used in modern financial modeling due to distortion risks.

Common Errors and Troubleshooting

Keep these validation rules in mind when calculating variance in Excel:

• #DIV/0! Error: Occurs in VAR.S if the referenced range contains only one numeric value (because n − 1 = 1 − 1 = 0, causing division by zero). VAR.S requires at least two numeric values.
• #VALUE! Error: Occurs if text or symbols are typed directly inside the function arguments (e.g. =VAR.S(10, 20, "text")).
• Ignored Cells: When passing cell ranges (e.g. A1:A100), both VAR.S and VAR.P automatically ignore empty cells, text strings, and boolean values.