How to Calculate Standard Deviation In Excel
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:
For examples of both population and sample standard deviation calculations in Excel, see the Standard Deviation Examples below.
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 updated and renamed. However, the old functions are still available in current versions of Excel, in order to maintain compatibility with older versions.
Comparison of Functions for Calculating Standard Deviation in Excel
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 |
Up to 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 |
Up to 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) |
STDEV.S vs. STDEVA and STDEV.P vs. STDEVPA
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:
Standard Deviation Examples
Example 1 - Calculate Population Standard Deviation 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 standard deviation of the sales figures using the Excel STDEV.P function. The formula for this is:
=STDEV.P( B3:B14, D3:D14, F3:F14 )
which returns the result 2,484.05.
Example 2 - Calculate Sample Standard Deviation 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 standard deviation of the stored height measurements using the Excel STDEV.S function. The formula for this is:
=STDEV.S( B3:B1002, D3:D1002, F3:F1002 )
which returns the result 5.40 cm.