How to Calculate Variance in Excel: Complete Guide

Microsoft Excel is well-known for providing various functions for statistical evaluation. If you have been using Excel for a while, you might be aware of the variance. Before using the variance formula in Excel, it’s essential to understand the function properly. In this guide, let’s explore some crucial information about the variance function.

What is a Variance Function in Excel?

Variance is a statistical measurement that determines the spread of values against the mean. For example, if you insert the data of exam results for a group of students, a variance function will let you know how well the students’ group has performed.

Excel’s variance function can help you by calculating the spread. Microsoft Excel has two different forms of variance; sample variance and population variance. You can use one of the six variance functions in Excel.

VAR is the original variance function while VAR.S is the newest one to enhance the speed of the original function. You can use VARP, VAR.P, and VARPA functions for population variance. In this case, VARP is the original one while VAR.P is the new replacement. However, you can use VARPA for logical and text strings.

How Do Variance Functions Work in Excel?

Before learning how to calculate variance in Excel, you have to understand how variance functions work. As noted before, there are six variance functions available in Excel. So, we will split them into two categories to deal with sample and population variation:

• Sample Variance — VAR, VAR.S, and VARA
• Population Variance — VARP, VAR.P, VARPA

VAR and VARP are considered outdated as there are two new replacements; VAR.S and VAR.P. The original functions can be removed from Excel in the future. From this list, four functions; VAR, VARP, VAR.S, and VAR.P focus on numerical data.

However, you have to use VARA and VARPA functions for mixed data as they support numbers, texts, and logical values. These two functions can make an impact on the overall results. That’s why it’s essential to choose the functions carefully.

Importance of Variance Calculation

Since variance is a significant statistical measurement, you have to understand its importance. Keep reading the following section to learn more:

• Identifying Causes of Variation: Analyzing the Excel variance can help you understand the causes. For example, you can identify the factors that affect the rate.

• Measuring Error Rates: With Excel variance, you can understand the rate of error while evaluating statistical data.

• Evaluating Differences Between Variables: Even though variance functions show how related certain variables are, they can also make differences between variables.

Things to Consider Before Using Variance in Excel

Before calculating variance in Excel, you have to be extra careful about choosing the functions. In this case, it’s also essential to check some considerations before using the variance:

• VAR.S is the newer replacement of VAR for sample data sets and should be used in the first place. Similarly, VAR.P should be used over VARP for population data sets as it’s the newer replacement.
• VAR, VAR.S, VARP, VAR.P only support numerical values. If you want to count logical and text values, you have to use VARA and VARPA functions.
• Excel lets you use references to cell ranges in your functions.
• You can use up to 254 different values if you want to reference each value separately. Since it’s an Excel limitation, it can’t be increased.
• If you want to add a text string as a value in the Excel formula, you have to reference it in another cell as directly adding it can cause a #VALUE error.

How to Calculate Variance in Excel

If you want to calculate variance in Excel, it’s essential to follow some crucial steps. In the following section, let’s explore the steps and understand how to calculate variance in Excel:

Step 1: Select an Empty Cell

First, open your Excel workbook containing data and select an empty cell. Besides that, you can also open a new workbook and ensure the containing data is opened. Once you have selected the empty cell, press the formula bar until you see the blinking cursor.

When the blinking cursor is visible, get ready to insert a new formula.

Step 2: Use Cell References to Insert Data

Before inserting the formula, you have to understand that all variance functions in Microsoft Excel use the same structures to create a formula. If you want to insert a function by using a sample data set, type =VAR.S or =VARA at the top of the formula bar. Similarly, you have to type =VAR.P or =VARPA for a population data set.

As the formula is opened, you have to insert your data. For example, =VAR.S(C2:C10) or =VAR.P(C2:C20) can complete the formula in a cell range between C2 and C10 or C2 and C20. Ensure you replace the reference with your own data.

If your data contains text and logical values, it’s best to use VARA or VARPA to count the overall results. Besides that, you can also reference each cell individually. It’s also applicable with VARA and VARPA.

For example, if =VARA(1,2,TRUE,3) works with the TRUE value, =VARPA(1,2,TRUE,3,4,10,8) can also count the value in the same way with TRUE value counting as 1. After adding a reference, close your formula and press Enter to check the result. Add a closing parenthesis after the final value is inserted.

Conclusion

If you have been searching how to calculate variance in Excel, you have to be extra careful about choosing the right functions. If you choose a wrong one, it will show an error in Excel. Do your research for a better understanding.

FAQs