Variance is basically a measure of how spread out your data is around its average. In Excel, it’s a key tool for seeing the variability in all sorts of data - from survey results to stock prices.
There are two main ways to think about variance: for the whole population and for just a sample. Population variance looks at every single data point, while sample variance deals with just a piece of the data.
Excel has three main functions for figuring out variance:
- VAR: This is the classic function, used to calculate the variance of a sample.
- VAR.S: A newer version, also for sample variance, introduced in Excel 2010.
- VAR.P: Introduced in Excel 2010 as well, this one computes the variance of an entire population.
VAR.S and VAR.P are the improved replacements for the older VAR function and are available if you’re using Excel 2010 or a later version.
Calculating sample variance using the VAR.S function in Excel
The VAR.S function is the go-to method for calculating sample variance, it was introduced in Excel 2010. It’s specifically designed to estimate variance from a sample.
The formula for VAR.S is:
=VAR.S(number1, [number2], …)
To calculate sample variance using numbers directly:
To calculate sample variance using cell references:
To calculate sample variance using a cell range:
You can mix individual numbers, cell references, and ranges in the same formula:
If you want to include text or logical values in the calculation of your sample variance, use the VARA function:
Calculating population variance using the VAR.P function in Excel
The VAR.P function is designed to find the variance of an entire population, and it is available in Excel 2010 and later. It’s the right choice when your data represents the whole group you’re interested in, and you want to measure how spread out the data points are in that total dataset.
The formula for VAR.P looks like this:
=VAR.P(number1, [number2], …)
To calculate population variance using only numbers:
To calculate population variance using cell references:
You can also use the VARPA function to compute population variance. It is similar to VAR.P, but includes numbers, text, and logical values in the calculation:
Calculating variance using the VAR function in Excel
In older versions of Excel, the VAR function is used to calculate variance for a sample. While VAR.S is a better alternative, knowing this is useful if you’re dealing with an older version of the program.
The formula for the VAR function is:
=VAR(number1, [number2], …)
You can use numbers, cell references, and ranges of cells:
Here’s an example of variance calculation using explicit numbers:
To calculate variance using cell references:
Calculating variance over a cell range:
You can also mix different kinds of arguments like individual numbers, cell references, and cell ranges:
You can see how simple it is to calculate variance using different methods in Excel.
