Variance is a measurement of the spread between numbers in a data set. The variance measures how far each number in the set is from the mean. You can use Microsoft Excel to calculate the variance of the data you have entered into a spreadsheet.
How to Calculate Variance in Excel
To calculate variance in Excel, you will need to have your data set already entered into the software. Once you have your data, you can choose your formula based on the type of data set you have and the type of variance you need to calculate.
There are a few different options for the formula to calculate variance in Excel:
- =VAR.S(select data)
- =VARA(select data)
- =VAR.P(select data)
For each of these, you would select the range of cells you want to use after the parentheses. For example, you might enter =VAR.S(B12:B32) to find the variance for the data in cells B12 to B32.
You must start with the "=" so that Excel knows you are entering a formula.
It is important to know what type of data you are working with in order to select the correct forumla. For example, if your data set contains any text values, VARA will interpret text as 0, TRUE as 1, and FALSE as 0, whereas VAR.S ignores all values other than numbers. This will change the result of your variance calculation.
VAR.P, on the other hand, is for calculating variance in a population based on the entire set of numbers. If you don't have the entire population of data to use, you should use VAR.S in your formula.
In general, VAR.S is the formula you should most often rely on for calculation variance in Excel.
What Is Variance?
What are you actually calculating when you use Excel to find the variance of a data set?
Using a data set chart, we can observe what the linear relationship of the various data points, or numbers, is. We do this by drawing a regression line, which attempts to minimize the distance of any individual data point from the line itself. In the chart below, the data points are the blue dots and the orange line is the regression line. The red arrows are the distance from the observed data and the regression line.
When we calculate a variance, we are asking, given the relationship of all these data points, how much distance do we expect on the next data point? This "distance" is called the error term, and it's what variance is measuring.
By itself, variance is not often useful because it does not have a unit, which makes it hard to measure and compare. However, the square root of variance is the standard deviation, and that is both practical and useful as a measurement.
Example of Calculating Variance in Excel
In the example below, we will calculate the variance of 20 days of daily returns in the highly popular exchange-traded fund (ETF) named SPY, which invests in the S&P 500. We will use the following formula:
In this instance, if we had all returns in the history of the SPY ETF in our table, we could use the population measurement VAR.P. However, since we are only measuring the last 20 days to illustrate the concept, we will use VAR.S.
As you can see, the calculated variance value of .000018674 tells us little about the data set, by itself. If we went on to square root that value to get the standard deviation of returns, that would be more useful.