How to Find Variance in Excel? Follow These Tips

Microsoft Excel is a ubiquitous product, and one that gets used for many organizational tasks. Most people are familiar with the program's basic format even if they don't own it themselves. In addition, simple operations such as adding or deleting columns or populating individual cells with data are easy to figure out with little instruction. There's a lot more to Excel than the basics, though: the program contains many logical and mathematical operations that are useful when dealing with large amounts of data, and these operations require more knowledge to use effectively. One of the most useful among these is the ability to calculate a value known as variance. Knowing how to find variance in Excel is a very helpful skill to have while designing any spreadsheet, especially if one understands the nuances of the function.

What Is Variance?

The Meaning of the Term

Two Kinds of Variance

How to Use Variance

The Six Variance Functions In Excel

Plenty of Options

Knowing how to find variance in Excel isn't as simple as knowing the one function that will calculate the value. Excel has six different functions that can calculate variance, each of which has unique properties. Using the functions well requires knowing the qualities of each and understanding what situations they work best in. Fortunately, Excel's six VAR functions can be split into three easily explained groups.

VAR.P and VAR.S: Population and Sample Functions

VAR.P and VAR.S are the most basic functions for calculating variance. The letter after the period specifies the variance being calculated: VAR.P calculates population variance, and VAR.S calculates sample variance.

These are the most commonly used functions, but there is one complication involved in using them: the function does not exist in older versions of Excel and opening a spreadsheet that uses it with one of these versions will cause compatibility issues.

VAR and VARP: The Old Functions

Before VAR.P and VAR.S were implemented, VAR and VARP were Excel's basic variance functions. Like the more modern functions, they calculate the two basic kinds of variance: VAR calculates sample variance while VARP calculates population variance. These functions may eventually be phased out of Excel, but for the time being, they exist in every version of the program.

Microsoft considers VAR and VARP 'compatibility functions', and they exist to provide reliable choices for spreadsheets that will be opened on older versions of the software. However, they are slightly less accurate than the modern functions.

VARA and VARPA: Assigned Values

Of all the variance functions in Excel, VARA and VARPA are probably the most complicated of the options. VARA and VARPA calculate sample variance and population variance, respectively, but these two functions interact differently with other contents within the spreadsheet.

While Excel's other four variance functions only recognize the content of referenced cells if that content is a number, VARA and VARPA read any assigned values within referenced cells or arrays. This means that VARA and VARPA will recognize written numbers in any cells or arrays referenced in the function: if one contains the word 'four', the functions will calculate the variance as though that cell contained the number 4. The functions will recognize all other text, including an empty cell, as a 0.

VARA and VARPA can also recognize logical values in the same way: if the contents of a cell or array contain the word 'TRUE', the functions will count it as a 1, and if they contain 'FALSE' the functions will count it as a 0.

If a spreadsheet contains many written elements or logical values these functions can save a lot of time, but they should be used cautiously. Overlooked text can lead to cells registering a value they shouldn't and causing an inaccurate result.

Calculating Variance Using Excel's VAR Function

How to Find Variance in Excel

Now that all of the individual variance functions in Excel have been explained, it's time to learn how to implement them. Fortunately, all six of the functions use the same syntax, and aside from the differences already mentioned, work in the same way.

In order to calculate the variance of a set, you must first select a cell in your spreadsheet you want to display the variance in. Then, type an '=' sign followed by the name of the function you intend to use into that box, followed by a set of parentheses.

Place the numbers you want the variance to be calculated from inside the parentheses, separated by commas: these arguments can be numbers, logical values, or arrays. The function should automatically calculate the variance of the data you have selected and display it in the cell.

Example Using the Arguments List

Now that we've explained the principle, here are some specific examples of how to find variance in excel. First, we'll use a simple example: This one only uses numbers written directly into the function as arguments and does not interact with other cells in the spreadsheet.

Let's say we have a set that contains the numbers 13, 15, 18, 17, 24, 22 and 10. We know that this a sample of a larger set and for the sake of this example we expect the spreadsheet to be opened on older versions of Excel, so we use VAR to calculate the sample variance. To do this, we type this into the cell:

=VAR(13, 15, 18, 17, 22, 10)

Example Using an Array

Now that we've seen a basic example of how to find variance in Excel, here's an example that's more in line with how you would use the functions in an average spreadsheet.

This time let's say we have a set that's contained in a 10 x 4 box of cells in our spreadsheet, with cell A1 at the top left-hand corner of the box and cell D10 in the bottom right. We use VAR to calculate the sample variance and this time we enter the arrays of the cells containing the data as the arguments. That will look like this:



With this, we've gone over everything you need to know about how to find variance in Excel. You've learned what variance is and have a general understanding of both its importance in statistics and how to derive useful values from it.
You've learned about population and sample variance and what situations each form of variance is used in. You've seen all six of the functions Excel provides for calculating the value. You know what functions to use to have your functions recognize assigned values, and you know which functions to use when compatibility is a concern.
You understand the syntax of the functions and have seen firsthand what the use of those functions looks like in various situations.

Congratulations! You are now fully prepared to design spreadsheets that calculate and display variance, and are one step closer to being an Excel pro!

Featured Image: Image by Christian Dorn from Pixabay

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.