Article updated on April 10, 2018

The VAR.P function returns the variance based on the entire 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: 170 and Set2 variance: 8600.

Excel Function Syntax



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


The equation for VAR.P 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 / 5 = 170.