The variance is a way to measure the spread of values in a dataset.
The formula to calculate population variance is:
σ2 = Σ (xi – μ)2 / N
where:
- Σ: A symbol that means “sum”
- μ: Population mean
- xi: The ith element from the population
- N: Population size
The formula to calculate sample variance is:
s2 = Σ (xi – x)2 / (n-1)
where:
- x: Sample mean
- xi: The ith element from the sample
- n: Sample size
We can use the VAR.S() and VAR.P() formulas in Excel to quickly calculate the sample variance and population variance (respectively) for a given dataset.
The following examples show how to use each function in practice.
Example 1: Calculating Sample Variance in Excel
The following screenshot shows how to use the VAR.S() function to calculate the sample variance of the values in column A:
The sample variance turns out to be 35.2079.
Example 2: Calculating Population Variance in Excel
The following screenshot shows how to use the VAR.P() function to calculate the population variance of the values in column A:
The population variance turns out to be 33.4475.
Notes on Calculating Sample & Population Variance
Keep in mind the following when calculating the sample and population variance:
- You should calculate the population variance when the dataset you’re working with represents an entire population, i.e. every value that you’re interested in.
- You should calculate the sample variance when the dataset you’re working with represents a a sample taken from a larger population of interest.
- The sample variance of a dataset will always be larger than the population variance for the same dataset because there is more uncertainty when calculating the sample variance, thus our estimate of the variance will be larger.
Additional Resources
The following tutorials explain how to calculate other measures of spread in Excel:
How to Calculate the Interquartile Range (IQR) in Excel
How to Calculate Weighted Standard Deviation in Excel
How to Calculate the Coefficient of Variation in Excel