Excel statistical functions: STDEVP

This article describes the STDEVP function in Microsoft Office Excel 2003 and in later versions of Excel. This article also describes how to use the function, and compares the returned value of the STDEVP function in Excel 2003 and in later versions of Excel with the returned values in earlier versions of Excel.

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

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

1. Create a blank Excel worksheet.
2. 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)

3. Click the Paste Options button, and then click Match Destination Formatting.
4. 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.

The Excel worksheet that you created contains the following items:

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

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 procedure:

1. 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.
2. 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.

Earlier versions of Excel return the wrong result for the STDEVP function because errors occur when Excel rounds off the values that it uses in the calculation. These errors 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 and in later versions of Excel

Excel 2003 and later versions of Excel use the following two-pass process:

1. 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.
2. 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. This is because the results of the second pass are independent of the value in cell D10. Therefore, the results in Excel 2003 and in later versions of Excel are more stable numerically.

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 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 or a later version of Excel.

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

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:

1. Calculate the sample mean.
2. Calculate each squared deviation.
3. 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 procedure is the calculator formula procedure:

1. Calculate the sum of the squares of all observations, the sample size, and the sum of all observations.
2. 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 and for later versions of Excel. These functions are improved because the one-pass procedure is replaced by 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.

Functions that are improved include the following functions:

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