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:

=VAR.P(B3:B7)

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

VAR.P(number1,[number2],...)

Arguments

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

Comments

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.