Maruf Islam
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.
In this article, we discuss Population Variance and ways to find Population Variance in Excel.
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.
Here,
- X – the population points.
- µ – calculated Mean.
- N – total number of population points.
Follow any of the following methods to find population variance in Excel.
Method 1: Using Conventional Formula to Calculate Population Variance in Excel
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.
- 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)
- The AVERAGE function results in the Mean.
=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
- The final depiction of the worksheet may look like the below image.
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
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 Excel 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.
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.
Do check out our awesome website, ExcelDemy. There are hundreds of articles regarding Excel and its issues.
Related Articles
- Budget vs Actual Variance Formula in Excel (with Example)
- How to Calculate Percentage Variance between Two Numbers in Excel
- Calculate Pooled Variance in Excel (with Easy Steps)
- How to Calculate Coefficient of Variance in Excel (3 Methods)
- Calculate Mean Variance and Standard Deviation in Excel
- How to Calculate Variance Using Pivot Table in Excel (with Easy Steps)
- Calculate Variance Percentage in Excel (3 Easy Methods)
