How to calculate variance in Excel?

In this lesson you will learn how to calculate variance in Excel.

What is the variance?

This variance is a statistical measure of variation. It means that the higher variance the more different values.

 

Variance in Excel

There are 2 basic functions which you can use to calculate variance in Excel.

VAR – common function which calculates variance in every version of Excel. However since Excel 2010 version this function is started to be improved and replaced by VAR.S. Although this feature is still available

VAR.S - calculates the sample variance of a supplied set of values. This is new function in Excel 2010 and its not working in earlier versions of Excel.

 

With VAR and VAR.S functions you can calculate variance for sample of values. To calculate variance for whole population of values just use VAR.P function.

 

Synax of variance functions:

=VAR(number1,[number2],...])

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

 

Examples of VAR function in Excel

 

Example 1 the simplest syntax of variance

As arguments of function you can use just numbers.

=VAR(2,3,4,5,6,7,8,125)

 

Example 2 cells as arguments in formula

Arguments could be also cells where you have some numbers.

=VAR(A2,A3,A4,A5)

 

Example 3 range of cells as arguments in formula

You can also use range of cells.

=VAR(A2:A10,B4:C10)

 

Example 4 mix of above examples in one formula

It is not a problem to use every kind of above arguments in one formula.

=VAR(A2:A10,B4,5,7)

Excel variance examples

Syntax of VAR.S is the same as VAR.