Excel VAR.P function


The Excel VAR.P function calculates the variance of a dataset representing an entire population. Unlike VAR, which is used for samples, VAR.P provides a measure of how much the numbers in the data set differ from the mean (average) for the whole population. This function is crucial for statistical analysis when you have access to all data points in the group you’re studying, offering insights into the population’s variability or consistency.
					=VAR.P(number1, [number2], ...)
  • number1: The first number, cell reference, or range in the population.
  • number2, …: [Optional] Additional numbers, cell references, or ranges, up to 255.
Return value
The variance of the population.

How to use

Input directly numbers or specify cell references/ranges containing the population data. VAR.P focuses solely on numeric values, ignoring text, logical values, and empty cells, to calculate the variance.


Simple VAR.P
Calculating Population Variance for Annual Sales: To evaluate the consistency of annual sales across all company branches.
If A1:A50 contains yearly sales figures for each branch, VAR.P calculates the variance of these sales, indicating how much individual branch sales deviate from the overall average sales figure.
VAR.P with Direct Values
Assessing Variability Among Specific Data Points: To determine the spread of specific revenue figures across an entire business sector.
					=VAR.P(12000, 13000, 12500, 14000, 13500)
This formula calculates the variance of directly specified revenue figures, offering a measure of how these revenues vary from their average, which is essential for sector-wide financial analysis.
VAR.P Across Multiple Ranges
Understanding Dispersion in Combined Data Sets: To calculate the variance for overall employee satisfaction ratings from multiple departments.
					=VAR.P(B2:B20, D2:D20)
Assuming B2:B20 and D2:D20 contain satisfaction ratings from two different departments, VAR.P evaluates all the ratings to compute the variance, providing insight into the overall variability or consistency in employee satisfaction.

Additional Notes

  • VAR.P is the preferred function when analyzing the variance of data representing an entire population or when each data point in the group is considered in the analysis.
  • For data representing a sample of a larger population, VAR.S (or VAR for earlier Excel versions) should be used to estimate the variance accurately.
  • Understanding the distinction between population and sample variance is critical for correct statistical analysis, affecting how variance is calculated and interpreted.

Related Functions

Excel VAR.S function

The Excel VAR.S function estimates variance based on a sample, aiding in the analysis of data spread and dispersion.

Excel STDEV.P function

The Excel STDEV.P function calculates standard deviation based on the entire population, key for precise variability measures.

Excel VAR function

The Excel VAR function calculates variance based on a sample, important for understanding data dispersion.

Content Navigation