Image source: https://pixabay.com/illustrations/office-windows-word-excel-1356793/
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
One of the most important steps towards understanding how to find variance in Excel is knowing what variance is. Variance is a value found in any large set of data that represents the average distance any single data value in the set is from the mean. If we made an equation to predict what the next data point in a set would be, the variance would be how far off the actual data point would likely be from that prediction.
Two Kinds of Variance
There are actually two forms of variance. Each of them is useful in a slightly different situation, and each value is calculated differently. The form of variance we just defined is also known as population variance. This means it’s the variance of a complete set of data, which distinguishes it from the other form of variance: sample variance.
Sample variance is a form of variance that’s calculated when you only have access to a sample of values from a larger set. Both variances represent the same thing and have the same uses, but they are each calculated differently. Fortunately, Excel has multiple functions for finding each.
How to Use Variance
While it can show the reliability of a set, variance by itself is not always a popular value to draw conclusions from. Because of how variance is calculated, it can be confusing: Variances are difficult to express meaningfully on a graph, and often aren’t expressed in the same units as the actual data within their set.
To find a value that can be better understood visually, one must take the square root of a set’s variance. This results in a value known as the standard deviation, which is expressed in the same unit as the data in the set and is closer to each point’s difference from the mean.
When you hear of a prediction that states the result ‘give or take’ a certain value, the value they’re talking about is the standard deviation. Fortunately, knowing how to find variance in excel also means knowing how to find this: To find a set’s standard of deviation, you just need to take the square root of the variance once it is calculated.
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!