Excel Statistical Functions: STDEVP (826406)

MORE INFORMATION

The STDEVP function returns the population standard deviation for a population whose values are contained in an Excel worksheet. You can specify up to 30 of these values in the argument (or arguments) to the STDEVP function.

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

Create a blank Excel worksheet.
Copy the table that follows these steps, click cell A1 in your blank Excel worksheet, and then click Paste on the Edit menu.

The entries in the table fill cells A1:D17 in your worksheet.
Click the Paste Options button, and then click Match Destination Formatting.
With A1:D17 (the pasted range) still selected, point to Column on the Format menu, and then click AutoFit Selection.

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((D4SUMSQ(A3:A8) - (SUM(A3:A8)^2))/(D4D4))
3	pre-Excel 2003 STDEVP v2	=SQRT((SUMSQ(A3:A8) - (SUM(A3:A8)^2)/D4)/D4)
5	Excel 2003 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((D13SUMSQ(A12:A17) - (SUM(A12:A17)^2))/(D13D13))
=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 STDEVP	=SQRT(DEVSQ(A12:A17)/D13)

The Excel worksheet that you created contains the following:

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, this value is the same as the value in cell D8.
Cell D8 contains the returned value of the STDEVP function in Excel 2003 (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 Excel 2002 and earlier.

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

In rows 10 to 17, you can experiment with modified data by adding a constant (in this case, a power of 10) to each data point. Typically, if you add a constant to each data point, the value of the sample variance does not change.

Try the following:

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

Notice the revised data values in cells A12:A17. Notice, also, 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. This is the correct behavior. However, the returned values in Excel 2002 and earlier change.

Earlier versions of Excel return the wrong result for the STDEVP function because errors that occur when Excel rounds off the values that it uses in the calculation affect the result more in the formula that these versions use.

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)

These intermediate values are combined in the formula that appears in the Help file in earlier versions of Excel.

Results in Excel 2003

Excel 2003 uses 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.

In the numeric examples, a high value for the power of 10 in cell D10 does not affect these squared differences because the results of the second pass are independent of the value in cell D10. Therefore, the results in Excel 2003 are more stable numerically.

Conclusions

Because Excel 2003 uses a two-pass process instead of a one-pass process, the returned value of the STDEVP function is more accurate in Excel 2003 than in earlier versions of Excel.

However, you are not likely to notice a difference between the results that Excel 2003 returns and the results that earlier versions of Excel return for most practical examples. 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 you want to determine whether your data will behave differently if you upgrade to Excel 2003, compare the returned values of the following functions:

STDEVP(values)

-and-

SQRT(DEVSQ(values)/COUNT(values))

If the returned values of these functions are consistent with the level of accuracy that you want, the value of the STDEVP function will not be affected when you upgrade to Excel 2003.

If you use Excel 2003, and you want to determine if 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)

-and-

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

This comparison provides a good approximation of the value of the STDEVP function as it is calculated in earlier versions of Excel.

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.

This procedure is more accurate than the alternative procedure. The alternative procedure is frequently referred to as the calculator formula because it is suitable for use on a calculator when you have a small number of data points. The following is the calculator formula procedure:

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).

There are many other functions that have been improved for Excel 2003 by replacing the one-pass procedure with the two-pass procedure that finds the sample mean on the first pass, and then calculates the sum of the squared deviations about the sample mean on the second pass. These functions include the following:

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

Similar improvements have been made in each of the three Analysis of Variance tools in the Analysis ToolPak.

For more information about STDEVP, click Microsoft Office Excel Help on the Help menu, type stdevp in the Search for box in the Assistance pane, and then click Start searching to view the topic.