How to Calculate Variance in Excel

Variance

With VAR.S and VAR.P functions you can calculate sample variance and population variance from given data in Excel 2010 and later versions.

Variance is a statistical calculation that is used to measure how far each number in the data set is from the mean. Variance is one of the most useful concepts in probability theory 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 calculated from population data, and sample variance is the variance calculated from sample data.

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

  • VAR is the most common function, which calculates variance in every version of Excel.
  • VAR.S functions are used to calculate variance for a sample of values.
  • VAR.P functions are used to calculate variance for the whole population of values.

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

Calculating Variance using VAR function in Excel

Use VAR function to estimate 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 one 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 find sample variance in Excel 2010 and later 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 numbers, use number 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 every kind of above arguments in one formula.

The Excel VARA function returns a sample variance based on a set of numbers, text, and logical values as shown below.

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 describes 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 calculate the variance of an entire population based on a set of numbers, text, and logical values.

That’s it. You can now easily calculate variance in Excel.