How to Find Population Variance in Excel (2 Easy Ways)

The formula for population variance evaluates the mean of the squared distances of data points from the population mean. Therefore, population variance can be defined as the average of the lengths between each data point and the mean squared for a given population. To ascertain the relationship between individual numbers in a data set, statisticians use this population variance.

In this article, we will discuss how to find population variance in Excel by using the variances functions. To see whether we get the same result, we will also use the basic population variance formula.

One can calculate population variance by using the following formula:

Microsoft Excel offers dedicated variance functions to apply the above formula.  For example, we have the exam scores of a group of 10 students C4:C13. The scores comprise the entire population. So, we will use the formula given in the image below to calculate the population variance.

Follow the two following methods to find the population variance in Excel:

Using Variance Functions

Excel offers three variants of variance functions to calculate population variance. Those are the VAR.P, VARP, and VARPA functions.

VARP Function

Excel versions from 2000 to 2019 have a working VARP function to calculate the population variance. This function takes only numbers as arguments.

• To calculate the population variance for the range of cell C4 to C13, use the formula: =VARP(C4:C13)
  

VAR.P Function

The VAR.P function is an updated version of the VARP function. Excel versions from 2010 onward support it. The VARP function is also available in newer versions of Excel.

• To calculate the population variance, use the formula:
  =VAR.P(C4:C13)
  

Note: It’s better to use the VAR.P function in the current Excel versions because the VARP function might not be available in the future Excel versions.

VARPA Function

The VARPA function finds the population variance of data comprising numbers, texts, and logical values. The function has been active since 2000 in Excel.

• To calculate the population variance, use the formula:
  =VARPA(C4:C13)
  

Note: You can enter up to 255 arguments in Excel 2007 and later, which correspond to a sample or population; you can enter up to 30 arguments in Excel 2003 and earlier.

Using Manual Formula

Earlier in this article, we mentioned the conventional population variance formula to calculate the population variance. Users need to compile all the required portions to finally find the population variance.

To calculate the population variance using the conventional formula, follow these steps:

1. Use the COUNT function to display the total number of population points (N), with the formula: =COUNT(C4:C13)
   2. Apply the AVERAGE function to calculate the mean (µ):
   =AVERAGE(C4:C13)
   3. Now, to find the distance of population points from the mean and square the distance ((X – µ)²), use the formula:
   =(C4-$G$5)^2
   As the population variance formula is = Σ (X – µ)² / N, you need to divide the summation of Column E by N.
   4. So, apply the formula to calculate the population variance:
   =SUM(E:E)/G4
   

So, the final result of the population variance will look like the image above.

Difference Between Excel VAR.P, VARP, and VARPA Functions

The VARPA function takes data types apart from numbers (i.e., texts and logical values). This function evaluates the text values as zero, whereas the VAR.P and VARP functions ignore them. All functions ignore the blank values. An overview of the differences between each function can be seen in the table below.

• To better understand the differences, we have applied these three variance functions to the same dataset. The VAR.P and the VARP functions have given the same results, while the VARPA function differs in result.
  

Download Practice Workbook

Conclusion

This article discusses population variance and ways to find population variance in Excel. Users can use the conventional formula or Excel functions to calculate population variance. Comment if you need further inquiries or have anything to add.

Frequently Asked Questions

What is the difference between population variance and sample variance?

Population variance looks at the spread of all the data in a group, while sample variance focuses on a part of that data (a sample). The way we calculate them is a bit different: when finding sample variance, we use a slightly adjusted formula n−1 in the denominator to account for potential errors.

Why square the differences in the variance formula?

Squaring the differences in the formula has two main purposes: it ensures that negative differences do not cancel out positive differences, and it gives greater weight to larger differences, emphasizing their impact on the overall variance.

How does population variance relate to standard deviation?

The standard deviation (σ) is the square root of the population variance (σ2). It is often used as a more interpretative measure of dispersion since it is in the same units as the original data.

<< Go Back to Calculate Variance in Excel | Excel for Statistics | Learn Excel