0.4 C
London
Friday, March 14, 2025
HomeExcelDescriptive Statistics in ExcelHow to Calculate Sample & Population Variance in Excel

How to Calculate Sample & Population Variance in Excel

Related stories

Learn About Opening an Automobile Repair Shop in India

Starting a car repair shop is quite a good...

Unlocking the Power: Embracing the Benefits of Tax-Free Investing

  Unlocking the Power: Embracing the Benefits of Tax-Free Investing For...

Income Splitting in Canada for 2023

  Income Splitting in Canada for 2023 The federal government’s expanded...

Can I Deduct Home Office Expenses on my Tax Return 2023?

Can I Deduct Home Office Expenses on my Tax...

Canadian Tax – Personal Tax Deadline 2022

  Canadian Tax – Personal Tax Deadline 2022 Resources and Tools...

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 = Σ (xix)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

Subscribe

- Never miss a story with notifications

- Gain full access to our premium content

- Browse free from up to 5 devices at once

Latest stories