How to Calculate Sample Variance in Excel (2 Effective Approaches)

Variance is one of the most useful topics in statistics. It gives you the measurement of how the data are spread around the mean. It calculates the distribution by looking at all the data. In this article, you will learn 2 ways to calculate sample variance in Excel.

Download Practice Workbook

You can download the following practice workbook that we have used to prepare this article.

What Is Sample Variance?

Normally the variance is calculated by dividing the square of the difference in the mean by the number of the population. In the sample variance, a sample is a chosen number of samples taken from a population.

For instance, if you want to measure American people’s heights, it won’t be practical (from either a monetary or a time standpoint) for you to calculate the heights of each person in the US population.

In this case, you need to take a sample of the population, such as 1000 people, and use this sample size to calculate the heights of the total population. The sample variance assists you to find the dispersion of your heights.

2 Ways to Calculate Sample Variance in Excel

1. Calculate Sample Variance by Applying Basic Mathematical Formula

The textbook formula for the sample variance is given in the following way.

Here,

• μ is the arithmetic mean
• X is the individual value
• N is the size of the population
• σ² is the sample variance

We want to calculate the sample variance of the 5 data (Individual Value, X). We have 2 columns which are for Deviation about the Mean (X-μ) and square of the Deviation about the Mean (X-μ)^2. Now, follow the steps below.

📌 Steps:

• First, determine the total number of data, in this example, N=5.
• Now, for computing the arithmetic mean for the individual values use the following formula,

• To get deviation about the mean (X-μ), in cell D5, type the following formula, then press ENTER, and drag the Fill Handle to D9.

• To get the square of the deviation about the mean (X-μ)^2, in cell E5, copy the following formula, press ENTER, and drag the Fill Handle to the remaining cells.

• To calculate the sum of the squares of the deviation about the mean (X-μ)^2, use the following formula in cell E11,

• Finally, to get the sample variance we need to divide the sum of the squares of the deviation about the mean (X-μ)^2 by the (N-1) and copy the following formula into cell E14.

Here is the result,

Read More: How to Calculate Mean Variance and Standard Deviation in Excel

2. Utilize Excel VAR.S Function

In order to calculate sample variance in Excel, we will use the built-in function VAR.S. In order to apply this function, just follow the steps below.

📌 Steps:

• First, in your dataset, select a cell (In this example, C11) where you want to put your Sample Variance value. Then, type the following formula into this cell, and finally, press ENTER.

Here is the result.

Read More: How to Calculate Variance in Excel (Easy Guide)

Conclusion

In this tutorial, I have discussed 2 ways to calculate sample variance in Excel. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• Budget vs Actual Variance Formula in Excel (with Example)
• Calculate Percentage Variance between Two Numbers in Excel
• How to Calculate Pooled Variance in Excel (with Easy Steps)
• Calculate Portfolio Variance in Excel (3 Smart Approaches)
• How to Calculate Coefficient of Variance in Excel (3 Methods)
• Calculate Variance Using Pivot Table in Excel (with Easy Steps)
• How to Calculate Variance Percentage in Excel (3 Easy Methods)