How to Calculate Variance In Excel
Sample and Population Variance
For a set of values representing an entire population, the Population Variance is give by the following equation:
where x takes on each value in the set, x is the average (statistical mean) of the set of values, and n is the number of values in the set.
If your data set is a sample of a population, (rather than an entire population), you should use a slightly modified form of the Variance, known as the Sample Variance. The equation for this is:
For examples of both population and sample variance calculations in Excel, see the Variance Examples below.
There are six different built-in functions for calculating variance in Excel. The Excel variance functions differ in the following ways:
- Some of the functions calculate the sample variance and some calculate the population variance.
- Some of the functions ignore text and logical values, while other functions treat these as numeric values (see Table 2 below for details).
Also, when Excel 2010 was released, two of the existing variance functions were updated and renamed. However, the old functions have not yet been removed from current versions of Excel, as Microsoft wishes to maintain compatibility with older versions of Excel.
Functions for Calculating Variance in Excel
The following table provides a description of the different types of variance function. This will help you to decide which of the functions you need to calculate a variance in Excel.
| Function | Version of Excel |
Population or Sample Variance |
Treatment of text & logical values |
|---|---|---|---|
| VAR.S |
2010 and later (new function in Excel 2010 - replaces the old VAR function) |
Sample | Ignored |
| VAR |
Up to 2007 (kept in current versions of Excel for compatibility, but may be discontinued in future versions of Excel) |
Sample | Ignored |
| VARA | 2003 and later | Sample |
Assigned values (see Table 2) |
| VAR.P |
2010 and later (new function in Excel 2010 - replaces the old VARP function) |
Population | Ignored |
| VARP |
Up to 2007 (kept in current versions of Excel for compatibility, but may be discontinued in future versions of Excel) |
Population | Ignored |
| VARPA | 2003 and later | Population |
Assigned values (see Table 2) |
VAR.S vs. VARA and VAR.P vs. VARPA
The VAR.S and VARA functions, and the VAR.P and VARPA differ only in the way they handle text and logical values that are supplied as a part of an array or range of cells.
For example, if a range of cells containing the logical value TRUE is supplied to the VAR function, this will return a different result to the same range of cells supplied to the VARA function.
The treatment of text and logical values supplied to the variance functions is summarised in the following table:
| Table2: Treatment of text & logical values supplied to Excel variance functions | ||
| Argument Type |
VAR.S, VAR, VAR.P & VARP |
VARA & VARPA |
|---|---|---|
| Logical values, within arrays or reference arguments | Ignored |
ARE counted (TRUE=1, FALSE=0) |
| Text (including empty text "", text representations of numbers, or other text), within arrays or reference arguments | Ignored | Counted as zero |
| Empty Cells | Ignored | Ignored |
| Logical values or text representations of numbers, typed directly into the list of arguments |
ARE counted (TRUE=1, FALSE=0) |
ARE counted (TRUE=1, FALSE=0) |
| Text that cannot be interpreted as a number, typed directly into the list of arguments | #VALUE! error | #VALUE! error |
Variance Examples
Example 1 - Calculate Population Variance in Current Versions of Excel
Cells B3-B14, D3-D14 and F3-F14 of the above spreadsheet on the right list a company's monthly sales figures, over three years.
If you have a current version of Excel (2010 or later), you can calculate the variance of the sales figures using the Excel VAR.P function. The formula for this is:
=VAR.P( B3:B14, D3:D14, F3:F14 )
which returns the result 6,170,524.69.
Example 2 - Calculate Sample Variance in Current Versions of Excel
The above example spreadsheet on the right stores the measurements (in cm) of 3,000 adult males. The measured heights are stored in cells B3-B1002, D3-D1002 and F3-F1002 of the spreadsheet.
If you have a current version of Excel (2010 or later), you can calculate the sample variance of the stored height measurements using the Excel VAR.S function. The formula for this is:
=VAR.S( B3:B1002, D3:D1002, F3:F1002 )
which returns the result 9.261904762 cm.