VAR.S Function

Estimates the variance of a sample.

Category

Statistical

Available in

Excel 2010+, Microsoft 365

Returns

A number

Want to practice VAR.S in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

Variance is the average squared distance from the mean. Because it is squared, it is in squared units (dollars², not dollars) — which is why most reports show the standard deviation, its square root, instead.

VAR.S treats the numbers as a sample. Use the .S (sample) version when your numbers are a sample of a bigger group — some customers, some days, some products — and the .P (population) version when they are the entire group you care about. The sample version divides by n − 1 instead of n, which makes it slightly larger to allow for the people you didn't measure.

In a range, text, TRUE/FALSE and empty cells are ignored. Numbers, logical values and numbers-as-text typed straight into the argument list are counted.

Syntax

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

Arguments

ArgumentRequired?What it does
number1YesA number, cell or range from the sample.
number2, …NoUp to 254 more numbers, cells or ranges.

Examples

Example 1 — Variance of sales

The same reps, treated as a sample.

E2fx=VAR.S(C2:C8)
ABCDE
1RepRegionSalesMeasureResult
2AnaNorth62000Variance106788928.57
3BenSouth48500Square root10333.87
4CyNorth50000
5DiEast71250
6EdSouth39800
7FayNorth55400
8GusEast48500
=VAR.S(C2:C8) Explain

The square root is exactly STDEV.S(C2:C8).

Example 2 — Built from DEVSQ

Sample variance is the sum of squared deviations divided by n − 1.

=DEVSQ(C2:C8)/(COUNT(C2:C8)-1) Explain

Returns the same 106,788,928.57.

Common mistakes

  • #DIV/0! There are fewer than two numbers.
  • Huge-looking numbers. Variance is in squared units, so it looks enormous next to the data. That's expected — compare standard deviations instead.

Notes

  • VAR.S replaces the older VAR function.
  • Statistical tests such as F.TEST compare variances directly.
  • Related functions: VAR.P, STDEV.S, VARA, DEVSQ.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.