# Excel statistical functions: STDEVP

### Summary

### More Information

### Syntax

STDEVP(value1, value2, value3, ...)In this example,

**value1**,

**value2**, and

**value3**represent values that are contained in an Excel worksheet.

Typically, the STDEVP function has only one value argument that specifies a range of cells that range of cells that contain the population. For example, STDEVP(A1:B100) uses A1:B100 for the argument. A1:B100 specifies the range of cells in the Excel worksheet that contain the population that the STDEVP function uses.

### Example of usage

- Create a blank Excel worksheet.
- Copy the following table, click cell
**A1**in your blank Excel worksheet, and then paste the entries so that the table fills cells A1:D17 in your worksheet.Data 6 population mean: =AVERAGE(A3:A8) 4 population size: =COUNT(A3:A8) 2 STDEVP =STDEVP(A3:A8) 1 pre-Excel 2003 STDEVP v1 =SQRT((D4*SUMSQ(A3:A8) - (SUM(A3:A8)^2))/(D4*D4)) 3 pre-Excel 2003 STDEVP v2 =SQRT((SUMSQ(A3:A8) - (SUM(A3:A8)^2)/D4)/D4) 5 Excel 2003 and in later versions of Excel STDEVP =SQRT(DEVSQ(A3:A8)/D4) Modified Data Power of 10 to add to data 1 =A3 + 10^$D$10 population mean: =AVERAGE(A12:A17) =A4 + 10^$D$10 population size: =COUNT(A12:A17) =A5 + 10^$D$10 STDEVP =STDEVP(A12:A17) =A6 + 10^$D$10 pre-Excel 2003 STDEVP v1 =SQRT((D13*SUMSQ(A12:A17) - (SUM(A12:A17)^2))/(D13*D13)) =A7 + 10^$D$10 pre-Excel 2003 STDEVP v2 =SQRT((SUMSQ(A12:A17) - (SUM(A12:A17)^2)/D13)/D13) =A8 + 10^$D$10 Excel 2003 and in later versions of Excel STDEVP =SQRT(DEVSQ(A12:A17)/D13) - Click the
**Paste Options**button, and then click**Match Destination Formatting**. - With A1:D17 (the pasted range) still selected, use one of the following procedures, as appropriate for the version of Excel that you are running:
- In Microsoft Office Excel 2007, click the
**Home**tab, click**Format**in the**Cells**group, and then click**AutoFit Column Width**. - In Excel 2003, point to
**Column**on the**Format**menu, and then click**AutoFit Selection**.

- In Microsoft Office Excel 2007, click the

- Cells A3:A8 contain six data points that are used in this example.
- Cell D5 contains the returned value of the STDEVP function for your current version of Excel. If you use Excel 2003 or a later version of Excel, this value is the same as the value in cell D8.
- Cell D8 contains the returned value of the STDEVP function in Excel 2003 and in later versions of Excel (regardless of the version of Excel that you are using).
- Cells D6 and D7 contain two approximations of the returned value of the STDEVP function that are calculated by using earlier versions of Excel. The formula in cell D6 is the formula that appears in the Help file for Microsoft Excel 2002 and earlier versions of Excel.

In these cells, the STDEVP function returns the value 1.707825128 in all versions of Excel.

Try the following procedure:

- Change the value in cell D10 to 2, 3, 4, 5, 6, or 7.

Notice the revised data values in cells A12:A17. Also notice that the STDEVP function behaves as expected in all versions of Excel when you use these values in cell D10. - Change the value in cell D10 to 8, 9, or 10.

Notice that the value of the population standard deviation remains 1.707825128 in Excel 2003 and in later versions of Excel. This is the correct behavior. However, the returned values in Excel 2002 and in earlier versions of Excel change.

Note The examples that appear in this article are extreme cases.

### Results in earlier versions of Excel

When the data contains many significant digits but has only a small variance, the formula that earlier versions of Excel use returns results that are not accurate. Earlier versions of Excel use a single pass through the data to calculate the following intermediate values:- The sum of squares of the data values
- The sum of the data values
- The count of the data values (sample size)

### Results in Excel 2003 and in later versions of Excel

Excel 2003 and later versions of Excel use the following two-pass process:- On the first pass, the sum of the data values and the count of the data values are calculated. The sample mean (average) is calculated from these results.
- On the second pass, the squared difference between each data point and the sample mean is found. These squared differences are summed.

### Conclusions

Because Excel 2003 and later versions of Excel use a two-pass process instead of a one-pass process, the returned value of the STDEVP function is more accurate in Excel 2003 and in later versions of Excel than in earlier versions of Excel.However, for most practical examples, you are not likely to notice a difference between the results that are returned in Excel 2003 and in later versions of Excel and the results that are returned in earlier versions of Excel. Typical data is not likely to behave the way that the data in this example behaves.

In earlier versions of Excel, numeric instability is most likely to appear when the data contains a high number of significant digits and relatively little variation between data values.

If you use an earlier version of Excel, and if you want to determine whether your data will behave differently if you upgrade to Excel 2003 or a later version of Excel, compare the returned values of the following functions:

- STDEVP(values)
- SQRT(DEVSQ(values)/COUNT(values))

If you use Excel 2003 or a later version of Excel, and if you want to determine whether the returned value of the STDEVP function is different from the returned value that you would receive if you used an earlier version of Excel, compare the returned values of the following functions:

- STDEVP(values)
- SQRT((SUMSQ(values) - (SUM(values)^2)/COUNT(values))/COUNT(values))

The following procedure calculates the sum of the squared deviations about a sample mean:

- Calculate the sample mean.
- Calculate each squared deviation.
- Sum the squared deviations.

- Calculate the sum of the squares of all observations, the sample size, and the sum of all observations.
- Calculate the sum of the squares of all observations minus ((sum of all observations)^2)/sample size).

Functions that are improved include the following functions:

- VAR
- VARP
- STDEV
- STDEVP
- DVAR
- DVARP
- DSTDEV
- DSTDEVP
- FORECAST
- SLOPE
- INTERCEPT
- PEARSON
- RSQ
- STEYX

Properties

Article ID: 826406 - Last Review: Apr 29, 2008 - Revision: 1