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:
Examples of VAR function in Excel
Example 1 the simplest syntax of variance
As arguments of function you can use just numbers.
Example 2 cells as arguments in formula
Arguments could be also cells where you have some numbers.
Example 3 range of cells as arguments in formula
You can also use range of cells.
Example 4 mix of above examples in one formula
It is not a problem to use every kind of above arguments in one formula.
Syntax of VAR.S (function for sample variance), VAR.P (function for variance of population) is the same as VAR.
Example 5 How to calculate sample variance using Data Analysis Toolpak Add-In?
First you need to make sure your Data Analysis Toolpak Add-In is installed. Here is the lesson how to install Data Analysis Toolpak Add-In.
Go to Ribbon to the Data tab. Click the Data Analysis button.
New window appears. Choose Descriptive Statistics and click OK.
Enter the proper parameters:
- Input Range - range of your data ($A$1:$C$10 in my example)
- Labels in first row - there are no labels in my data table
- Output Range - choose where you want to get your variance calculated (I chose the same Sheet $G$1:$T$18)
- Summary statistics - make sure it is checked
And this is how to see my Statistics summary. Sample variance is calculated in row 8.
Further reading: Statistical process control chart Calculating forecast accuracy and precision How to calculate Standard Deviation?