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

For scattered data points, not huge in size, users need to find population variance in Excel to identify dispersion. Population Variance has its own conventional formula. Also, Excel offers VAR.P, VARP, and VARPA functions to find population variance.

How to Find Population Variance in Excel

In this article, we discuss Population Variance and ways to find Population Variance in Excel.


Download Excel Workbook


Understanding Population Variance

Variance measures the distance of particular points from the mean. It’s also a parameter to refer to data dispersion from the average value within a sample dataset. Therefore, the greater the variance, the higher the dispersion of data points from the mean, or contrary. Population Variance works the same way. It indicates how scattered the population data points are. For huge datasets, users need to calculate the Sample Variance otherwise Population Variance.

Population Variance ( σ2 ) = Σ (X – µ)2 / N

Here,

  • X – the population points.
  • µ – calculated Mean.
  • N – total number of population points.

2 Easy Ways to Find Population Variance in Excel

Follow any of the following methods to find population variance in Excel.


Method 1: Using Conventional Formula to Calculate Population Variance

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

Population Variance ( σ2 ) = Σ (X – µ)2 / N
  • Enter the formula components (X – µ), (X – µ)2, N, Mean, Population Variance in the worksheet.
  • Use the latter COUNT formula to display the total number of population points.
=COUNT(C:C)

Counting Entries

=AVERAGE(C:C)

  • Now, find the distance of population points from the mean. Afterward, square the distance as shown in the below picture.
=C4-$G$5

  • As the Population Variance formula is = Σ (X – µ)2 / N, divide the Column E Sum by N.
=SUM(E:E)/G4

 Finding Population Variance in Excel

  • The final depiction of the worksheet may look like the below image.

⧭Note: You can add as many rows in the attached dataset as you like, and the values will be automatically calculated. You can find Sample Variance calculation with a similar approach.

Read More: How to Do Variance Analysis in Excel (With Quick Steps)


Method 2: Using VAR.P, VARP, or VARPA Functions to Find Population Variance in Excel

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

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

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

Excel versions from 2000 to 2019 have a working VARP function.

VARP(number1, [number2], …)

But the VARPA function finds population variance of data comprising Numbers, Texts, and Logical Values. The function has been active since 2000 in Excel.

VARPA(value1, [value2], …)
  • Use any of the variants to find population variance of Column C.

Read More: How to Calculate Variance in Excel (3 Handy Approaches)


Difference Between VAR.P, VARP, and VARPA Functions

As the VARPA function takes data types apart from numbers (i.e., takes also Texts and Logical Values), assigning an entire column result in a different value than its counterpart. The VAR.P and VARP functions are the same. Therefore, users need to specify the exact range in case they want to ignore data other than numbers.


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.

Do check out our awesome website, ExcelDemy. There are hundreds of articles regarding Excel and its issues.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo