As you’ve probably figured out by now, Excel has several different functions that can calculate variance in your data sets. The VAR.S function and the VAR.P function are both used to figure out the variance of the set of numbers you provide, but they work in slightly different ways. This post explains how each function works and shows you the difference between VAR.S and VAR.P in Excel.
What is the difference between VAR.S and VAR.P?
The main difference between sample variance and population variance is that population variance is used to calculate the variability of a population while sample variance is used to calculate the variability of a sample. Another difference between the two is that population variance is calculated using the entire population while sample variance is calculated using a sample of the population.
In excel you have two functions VAR.S and VAR.P to measure the variance, but they differ in how they calculate the variance. VAR.S calculates the variance based on the sample size, while VAR.P calculates the variance based on the population size.
How do you calculate variance in Excel?
You can calculate variance in Excel by using the VAR.S or VAR.P function. Suppose you have the below sales data table and calculate population variance and sample variance in excel.
The population variance VAR.P is calculated by taking the sum of the squared differences between each data point and the mean, and then dividing it by the number of data points.
The population variance is defined as: σ2 = ∑ (x – μ)2 / N where μ is the population mean and N is the population size.
The sample variance VAR.S is calculated by taking the sum of the squared differences between each data point and the mean, and then dividing it by the number of data points in your sample.
The sample variance is defined as: s2 = ∑ (x – x̄)2 / (n – 1) where x̄ is the sample mean and n is the sample size.
Please follow the below steps to calculate population and sample variance in excel
- Go to cell D3 and type the text Population Variance
- Go to cell D4 and type the text Sample Variance
- Type the formula =VAR.P($B$2:$B$21)) in cell E3
- Type the formula =VAR.S($B$2:$B$21)) in cell E4
Which variance function should you use?
The VAR.S function should be used when calculating variance for a sample, and the VAR.P function should be used when calculating variance for a population.
Conclusion
In conclusion, the VAR.S function is generally used to measure how much variance is based on the sample. VAR.P function is used to measure how much variance is based on the entire population.
