Author: Oscar Cronquist Article last updated on April 10, 2018

The VAR.S function tries to estimate the variance based on a sample of the population. The function ignores logical and text values.

Variance shows how much a set of numbers are spread out from their average value.

Formula in cell B13:


The formula calculates the variance of the numbers in cell range B3:B7.

Both Set1 and Set2 above have the same average 30, however, values in Set2 are much more spread out.

Set1 variance: 212.5 and Set2 variance: 10750.

Excel Function Syntax



number1 Required.  A cell reference to the sample of the population.
number2 Optional. Up to 254 additional arguments.


The equation for VAR.S is:

x ̅  is the sample mean AVERAGE(number1,number2,…)

n is the sample size.

Using the example above (Set1), the average of 10, 30, 25, 50 and 35 is 30.

(10-30)^2+(30-30)^2+(25-30)^2+(50-30)^2+(35-30)^2 = 850

850 / 4 = 212.5.