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)
Syntax of VAR.S is the same as VAR.
Further reading: Statistical process control chart Calculating forecast accuracy and precision How to calculate Standard Deviation?