How does VAR.P differ from the function used for sample data?

VAR.P assumes your entire dataset is the population of interest and divides by n (count). For sample data, use a different function that divides by n-1 to correct for sampling bias and provide an unbiased variance estimate.

Can I calculate VAR.P across multiple non-adjacent ranges?

Yes. You can pass multiple ranges or individual cell references separated by commas, e.g., =VAR.P(A2:A5, C2:C5, D10). VAR.P treats all supplied values as one dataset and calculates variance across all of them together.

Why is the variance number so large and hard to compare to my original data?

Variance is always in squared units, which inflates the magnitude. Take the square root (=SQRT(VAR.P(...))) to get standard deviation, a measure of spread in the same units as your original data and much easier to interpret.

What should I conclude if VAR.P returns zero?

A variance of zero means all values in your dataset are identical. For instance, if every channel drove exactly 2,000 clicks, VAR.P would return 0 because there is no variation around the mean.

VAR.P function

VAR.P returns the population variance of a dataset, showing spread from the mean; use it when your data represents the entire population, not a sample.

=VAR.P(number1, ...)

Generate a VAR.P formula

Describe what you need. The generator will reach for VAR.P where VAR.P is the right tool, and tell you when it is not.

Arguments

How VAR.P reads its arguments

Argument	Required	Description
number1	Required	Required. A number, range, or cell reference to include in the variance calculation.
...	Repeating	Optional. Additional numbers, ranges, or cell references; you can provide up to 254 arguments total.

Returns

Returns a number representing the population variance (spread from the mean, in squared units).

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Calculate variance in campaign spending across all channels

Campaign	Channel	Spend	Clicks	Conversions
Q4 Sale	Email	500	2000	50
Q4 Sale	Social	800	3000	75
Q4 Sale	Search	1200	4500	100
New Year	Email	600	2500	60
New Year	Social	900	3500	85
New Year	Search	1400	5000	120

=VAR.P(C2:C7)

Result: 100000

Spending ranges from $500 to $1,400 across six campaigns. The mean spend is $900. VAR.P calculates how far each spend deviates from the mean, squares those deviations, and averages them. The result (100,000) is in squared dollars, reflecting substantial variation in budget allocation across channels and campaigns.

2. Measure variance in click volume across all channels

Campaign	Channel	Spend	Clicks	Conversions
Q4 Sale	Email	500	2000	50
Q4 Sale	Social	800	3000	75
Q4 Sale	Search	1200	4500	100
New Year	Email	600	2500	60
New Year	Social	900	3500	85
New Year	Search	1400	5000	120

=VAR.P(D2:D7)

Result: 1222222.22

Clicks range from 2,000 to 5,000, with an average of 3,416.67. VAR.P computes the variance as 1,222,222.22 (in squared clicks), showing clicks vary more than spending does. This high variance indicates that some channels drive significantly more traffic than others, useful for identifying high-performing marketing channels.

3. Analyze consistency of Q4 Sale campaign conversions by channel

Campaign	Channel	Conversions
Q4 Sale	Email	50
Q4 Sale	Social	75
Q4 Sale	Search	100

=VAR.P(50,75,100)

Result: 416.67

The three Q4 channels delivered 50, 75, and 100 conversions respectively, with a mean of 75. VAR.P returns 416.67 (in squared conversions), indicating moderate inconsistency across channels. Search significantly outperforms the other two, making this campaign unbalanced; use this metric to evaluate whether budget reallocation could improve overall consistency.

Common errors

Which VAR.P error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	The range includes text values like 'Email' or 'Social' that cannot be interpreted as numbers.	Ensure your range contains only numeric data. Reference a numeric column (Spend, Clicks, or Conversions) instead of the Channel or Campaign name column.
#REF!	A cell reference in the range has been deleted, renamed, or moved to a different sheet, breaking the link.	Update the formula to point to valid, existing cells, or use IFERROR to gracefully handle missing references.
#N/A	One of the cells in the range contains #N/A (typically from a failed VLOOKUP, XLOOKUP, or similar lookup function).	Debug your lookup formula to return a valid number, or wrap it with IFNA/IFERROR to replace the error with a default numeric value before VAR.P processes it.

Tips and when to use something else

VAR.P ignores empty cells completely; they do not count as zero. Be sure your range contains no accidental blanks, or use COUNTA to verify the count of values before calculating variance.
Use VAR.P only for population data. If your values are a sample from a larger group, you need a different function that corrects for sample bias.
Variance is in squared units (squared dollars, squared clicks, etc.), making it hard to interpret. Convert to standard deviation by taking SQRT(VAR.P(...)) to get a value in the original units.
If your data includes outliers or extreme values, variance will be very high. Identify outliers with MAX and MIN, then decide whether to exclude them before recalculating VAR.P.

Related functions

AVERAGEAVERAGE calculates the arithmetic mean of numbers, ignoring text and logical values; returns a single numeric value representing the average.COUNTCOUNT counts the number of cells containing numeric values and dates in one or more ranges, ignoring text and blank cells.MAXMAX returns the largest numeric value in a range, ignoring text and empty cells. Useful for finding peak inventory, highest prices, or latest dates.MINMIN finds and returns the smallest numeric value in a range or list, automatically excluding text and empty cells.IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.

Frequently asked questions

How does VAR.P differ from the function used for sample data?: VAR.P assumes your entire dataset is the population of interest and divides by n (count). For sample data, use a different function that divides by n-1 to correct for sampling bias and provide an unbiased variance estimate.
Can I calculate VAR.P across multiple non-adjacent ranges?: Yes. You can pass multiple ranges or individual cell references separated by commas, e.g., =VAR.P(A2:A5, C2:C5, D10). VAR.P treats all supplied values as one dataset and calculates variance across all of them together.
Why is the variance number so large and hard to compare to my original data?: Variance is always in squared units, which inflates the magnitude. Take the square root (=SQRT(VAR.P(...))) to get standard deviation, a measure of spread in the same units as your original data and much easier to interpret.
What should I conclude if VAR.P returns zero?: A variance of zero means all values in your dataset are identical. For instance, if every channel drove exactly 2,000 clicks, VAR.P would return 0 because there is no variation around the mean.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17