*22*

The **variance **is a way to measure the spread of values in a dataset.

The formula to calculate **population variance** is:

**σ ^{2}** = Σ (x

_{i}– μ)

^{2}/ N

where:

**Σ**: A symbol that means “sum”**μ**: Population mean**x**: The i_{i}^{th}element from the population**N**: Population size

The formula to calculate **sample variance** is:

**s ^{2}** = Σ (x

_{i}– x)

^{2}/ (n-1)

where:

**x**: Sample mean**x**: The i_{i}^{th}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