Cellwise Start free

Statistics function

Excel VAR.P function

VAR.P returns the variance of a whole population: the average squared distance of each value from the mean, divided by n. Use it when your range holds every value you care about, such as all six stores in a region. If the numbers are only a sample, use VAR.S.

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

Arguments

number1
The first number, cell or range.
number2
Optional. More numbers or ranges. Text, TRUE/FALSE and empty cells inside a range are skipped.

VAR.P examples

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

AB
1StoreWeekly sales ($k)
2Northgate42
3Riverside38
4Old Town45
5Harbor40
6Westfield35
7Elm Park40

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

  1. =ROUND(VAR.P(B2:B7),2)

    Result: 9.67

    The six stores average $40k. Their squared distances from 40 add up to 58, and 58 / 6 is a variance of 9.67.

  2. =ROUND(VAR.S(B2:B7)-VAR.P(B2:B7),2)

    Result: 1.93

    VAR.S divides the same 58 by 5 instead of 6, so it is 1.93 higher. Pick by what the range is, not by which answer looks nicer.

  3. =ROUND(SQRT(VAR.P(B2:B7)),2)

    Result: 3.11

    The square root is the population standard deviation, $3.11k, the same as STDEV.P(B2:B7).

  4. =VAR.P(B2:B7)*COUNT(B2:B7)

    Result: 58

    Variance times the count gives back the sum of squared deviations, 58, which DEVSQ(B2:B7) returns directly.

How to use VAR.P

  1. Click the cell for the result.
  2. Type =VAR.P( and select the numbers.
  3. Press Enter.
  4. Use SQRT or STDEV.P to turn it into a standard deviation.

Common VAR.P mistakes

Using VAR.P on a sample

Survey answers, test batches and a few weeks of data are samples. VAR.P on a sample underestimates the spread; use VAR.S.

Comparing variances in different units

Variance is in squared units, so a variance of dollars and one of thousands of dollars differ by a factor of a million. Compare standard deviations instead.

Text numbers in the range

Numbers stored as text are skipped without warning. Check that COUNT(B2:B7) matches the number of rows.

VAR.P FAQ

When should I use VAR.P instead of VAR.S?

Use VAR.P when the data is the complete group, every member and not a selection. In most business analysis the numbers stand for something bigger, so VAR.S is the usual choice.

Is VARP the same as VAR.P?

Yes. VARP is the older name and returns the same result. Excel keeps it for compatibility.

What does VAR.P return for one number?

0, because a single value does not differ from its own mean. VAR.S returns #DIV/0! for one number.

Guides that use VAR.P

Related functions

Updated