ExcelFunctions.net

Search site:

Sample and Population Standard Deviations

For a set of values representing an entire population, the Population Standard Deviation 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.

When your data set is a __sample__ of a population, (rather than an entire population),
you should use a slightly modified form of the Standard Deviation, known as the
Sample Standard Deviation.
The equation for this is :

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

The main differences between the Excel standard deviation functions are:

- whether the sample standard deviation or the population standard deviation 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 the standard deviation 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.

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

Function | Version of Excel |
Population or Sample Standard Deviation |
Treatment of text & logical values |

STDEV.S |
2010 (new function in Excel 2010 - replaces the old STDEV function) |
Sample | Ignored |

STDEV |
2003 & 2007 (kept in Excel 2010 for compatibility, but may be discontinued in future versions of Excel) |
Sample | Ignored |

STDEVA | 2003, 2007 & 2010 | Sample |
Assigned values (see Table 2) |

STDEV.P |
2010 (new function in Excel 2010 - replaces the old STDEVP function) |
Population | Ignored |

STDEVP |
2003 & 2007 (kept in Excel 2010 for compatibility, but may be discontinued in future versions of Excel) |
Population | Ignored |

STDEVPA | 2003, 2007 & 2010 | Population |
Assigned values (see Table 2) |

The STDEV.S and STDEVA functions, and the STDEV.P and STDEVPA 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 STDEV function, this will return a different result to the same range of cells supplied to the STDEVA function.

The treatment of text and logical values supplied to the standard deviation functions is shown in the following table:

Table2: Treatment of text & logical values supplied to Excel standard deviation functions

Argument Type |
STDEV.S, STDEV, STDEV.P & STDEVP |
STDEVA & STDEVPA |

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 |