Cellwise Start free

Statistics function

Excel VAR.S function

VAR.S returns the variance of a sample: the average squared distance of each value from the mean, divided by n - 1. Use it when your numbers are a sample of something bigger, such as a few filled bags from a production run. Variance is in squared units, so most reports show its square root, the standard deviation, instead.

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

Arguments

number1
The first number, cell or range of the sample.
number2
Optional. More numbers or ranges, up to 254 in all. Text, TRUE/FALSE and empty cells inside a range are skipped.

VAR.S examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

ABC
1BatchFill (g)Line
2B-01502L1
3B-02498L1
4B-03505L2
5B-04497L2
6B-05501L1
7B-06503L2
8B-07499L1
9B-08495L2

Download this sheet as an Excel practice file (.xlsx)

  1. =ROUND(VAR.S(B2:B9),2)

    Result: 11.14

    The eight fills average 500 g. Their squared distances from 500 add up to 78, and 78 / 7 is a sample variance of 11.14 square grams.

  2. =ROUND(VAR.S(FILTER(B2:B9,C2:C9="L2")),2)

    Result: 22.67

    The variance for line L2 only: 22.67. Line L2 is far less consistent than the whole sample suggests. FILTER needs Excel 365 or 2021.

  3. =ROUND(SQRT(VAR.S(B2:B9)),2)

    Result: 3.34

    The square root of the variance is the standard deviation, 3.34 g, back in the units of the data. STDEV.S(B2:B9) gives the same number in one step.

  4. =VAR.S(B2)

    Result: #DIV/0!

    One value has no spread to measure, so VAR.S returns #DIV/0!. It needs at least two numbers.

How to use VAR.S

  1. Click the cell for the result.
  2. Type =VAR.S( and select the numbers.
  3. Press Enter.
  4. Wrap it in SQRT, or use STDEV.S, if you want the spread in the original units.

Common VAR.S mistakes

Using VAR.S on every value there is

If the range holds the whole population, every store or every employee, use VAR.P. VAR.S divides by n - 1 and comes out slightly larger.

Reading variance in the data’s units

A variance of 11.14 means 11.14 square grams, not 11 grams of spread. Take the square root, or use STDEV.S, before you explain it to anyone.

Zeros standing in for missing data

Empty cells are skipped but zeros count, and one 0 among values near 500 makes the variance explode. Leave missing readings empty.

VAR.S FAQ

What is the difference between VAR.S and VAR.P in Excel?

VAR.S treats the numbers as a sample and divides by n - 1. VAR.P treats them as the whole population and divides by n. With eight values the gap is about 14%; with hundreds it is tiny.

Is VAR the same as VAR.S?

Yes. VAR is the older name for the sample variance and gives the same result. VARP is the older VAR.P. Microsoft recommends the .S and .P names in new workbooks.

How do I calculate variance by group in Excel?

In Excel 365, =VAR.S(FILTER(B2:B9,C2:C9="L2")) gives the variance for one group. In older versions, =VAR.S(IF(C2:C9="L2",B2:B9)) does the same when confirmed with Ctrl+Shift+Enter.

Why does VAR.S return #DIV/0!?

The range holds fewer than two numbers. Numbers stored as text do not count, so check the cells with ISNUMBER.

Guides that use VAR.S

Related functions

Updated