How to Calculate Variance in Excel

Variance is a statistical calculation that is used to measure how far each number in the data set is from the mean. Variance calculation is one of the most helpful concepts in probability and statistics. A few of the good examples where the variance is used are polls, stock markets, investment returns, etc.

There are actually two forms of variance: sample variance and population variance. Population variance is the value of variance that is based on population data, and sample variance is the variance that is based on sample data.

Excel has three different types functions that you can use to calculate variance:

VAR is the most widely used function, which calculates variance in all versions of Excel.
VAR.S function is used to calculate variance for a sample of values.
VAR.P function is used to calculate variance for the whole population of data.

Both VAR.S and VAR.P are the advanced versions of the VAR function, which are only available in Excel 2010 and higher versions.

Calculating Variance using VAR function in Excel

Use VAR function to calculate variance based on sample data in older versions of Excel.

The formula for variance function is:

=VAR(number1, [number2], …)

You can calculate variance on numbers, cells, range of cells, and a combination of all three.

We’ll use the following example of students scores to find variance.

To find variance on just numbers, use number as arguments of the function.

To find variance on values of multiple cells, use cell references as arguments of the function.

To find variance on ranges of cells, use cell ranges as arguments of the function.

You can also find variance on every kind of above arguments in this formula.

Calculating Sample Variance using VAR.S function in Excel

VAR.S is an advanced and improved version of the Excel VAR function. You can use the VAR.S function to estimate sample variance of the sample data in Excel 2010 and newer versions. VAR.S function estimates the variance assuming given data is a sample.

Syntax of sample variance functions:

VAR.S(number1, [number2], …)

To find sample variance on numerical values, use numbers as arguments of the function.

To find sample variance on values of the cell, use cell references as arguments of the function.

If you want to find sample variance on a range of cells, use the cell range as an argument of the function.

You can also find sample variance on different kind of arguments in one formula.

You can also use Excel VARA function to estimate a sample variance based on a set of numbers, text, and logical values.

Calculating Population Variance using VAR.P function in Excel

VAR.P is another new and improved version of the Excel VAR function. You can use this function to find population variance in Excel 2010 and later versions. The Excel VAR.P function gives the variance from a whole population. It shows how data points in the entire population are spread out.

Syntax of population variance functions:

VAR.P(number1, [number2], …)

To find population variance on numbers, use numbers as arguments of the function as shown below.

To find sample variance on cell values, use cell references as arguments of the function.

You can use the VARPA function to find the variance for data that represents an entire population based on a set of numbers, texts, and logical values.