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



There are six different built-in functions for calculating variance in Excel. The main differences between the Excel variance functions are:

Also, when Excel 2010 was released, two of the existing variance functions were renamed. However, in order to maintain compatibility with older versions, Excel 2010 and Excel 2013 have also kept the old named functions.


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 should be used when calculating variance in Excel.



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

Return to the Excel Formulas page

Return to the ExcelFunctions.net Home Page

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2015 ExcelFunctions.net