ExcelFunctions.net

Calculating Variance In Excel

Home » Excel-Formulas » variance-in-excel

There are six different built-in functions for calculating variance in Excel, so it can be confusing when deciding which function to use.

The main differences between the Excel variance functions are:

  • whether the sample variance or the population variance is calculated.
  • whether text and the logical values, supplied as a part of an array (or stored in a supplied range of cells) are ignored or are treated as having values. Details of the differences are outlined in Table 2 below.

Also, Excel 2010 has renamed two of its variance functions from older versions of Excel. However, to maintain compatibility with older versions, Excel 2010 has also kept the old named functions, which it stores in its list of "Compatibility" functions.


Functions for Calculating 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:

Population Variance 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.

When 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 :

Sample Variance Equation

The following table provides a description of the different types of variance function. This will help you to decide which of the functions should be used when calculating variance in Excel.

Function Version of Excel Population or Sample
Variance 		Treatment of text
& logical values
VAR.S 2010
(new function in Excel 2010 -
replaces the old VAR function) 		Sample Ignored
VAR 2003 & 2007
(kept in Excel 2010 for compatibility,
but may be discontinued in
future versions of Excel) 		Sample Ignored
VARA 2003, 2007 & 2010 Sample Assigned values
(see Table 2)
VAR.P 2010
(new function in Excel 2010 -
replaces the old VARP function) 		Population Ignored
VARP 2003 & 2007
(kept in Excel 2010 for compatibility,
but may be discontinued in
future versions of Excel) 		Population Ignored
VARPA 2003, 2007 & 2010 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 shown 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