ExcelFunctions.net

Search Site:

Sample and Population Standard Deviations

Excel provides functions for calculating both the Population Standard Deviation and the Sample Standard Deviation.

The Population Standard Deviation is used for a set of values representing an entire population and is calculated 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 the 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. The main differences between the Excel standard deviation functions are:

- Some of the functions calculate the sample standard deviation and some calculate the population standard deviation;
- 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 standard deviation functions were renamed. However, in order to maintain compatibility with older versions, current versions of Excel have also kept the old named functions.

Table 1 (below) 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.

Table 1: Comparison of the Excel Standard Deviation Functions | |||

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

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

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

STDEVA | 2003 and later | Sample | Assigned values (see Table 2) |

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

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

STDEVPA | 2003 and later | 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 summarised in the following table: