VAR.P Function

Calculates the variance of an entire population.

Category

Statistical

Available in

Excel 2010+, Microsoft 365

Returns

A number

Want to practice VAR.P in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

VAR.P returns the average squared distance from the mean, treating the numbers as the whole group: the sum of squared deviations divided by n.

Use the .S (sample) version when your numbers are a sample of a bigger group — some customers, some days, some products — and the .P (population) version when they are the entire group you care about. The sample version divides by n − 1 instead of n, which makes it slightly larger to allow for the people you didn't measure.

In a range, text, TRUE/FALSE and empty cells are ignored. Numbers, logical values and numbers-as-text typed straight into the argument list are counted.

Syntax

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

Arguments

ArgumentRequired?What it does
number1YesA number, cell or range covering the whole population.
number2, …NoUp to 254 more numbers, cells or ranges.

Examples

Example 1 — Variance across the whole team

All seven reps are the population.

E2fx=VAR.P(C2:C8)
ABCDE
1RepRegionSalesMeasureResult
2AnaNorth62000Population91533367.35
3BenSouth48500Sample106788928.57
4CyNorth50000
5DiEast71250
6EdSouth39800
7FayNorth55400
8GusEast48500
=VAR.P(C2:C8) Explain

VAR.P × n ÷ (n − 1) gives VAR.S: 91,533,367.35 × 7 ÷ 6 = 106,788,928.57.

Common mistakes

  • #DIV/0! The range contains no numbers.
  • Squared units. The result is in dollars², so take the square root (or use STDEV.P) to get back to dollars.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.