Author: Oscar Cronquist Article last 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.