Description of the STDEV function in Excel 2003 and in later versions of Excel

Summary

The purpose of this article is to describe the STDEV function in Excel 2003, and in later versions of Excel, to illustrate how the function is used, and to compare results of the function for Excel 2003 and later versions of Excel with results of STDEV when used in earlier versions of Excel.

More Information

STDEV returns the sample standard deviation for a sample whose values are contained in an Excel worksheet and specified by the argument or arguments to STDEV.

Syntax

STDEV(value1, value2, value3, ...)
where value1, value2, ..., up to 30 value arguments.

The most common usage of STDEV includes only 1 value argument specifying a range of cells that contain the sample, for example, STDEV(A1:B100).

Example Usage

Create a blank Excel worksheet, copy the table below, select cell A1 in your blank Excel worksheet, and then paste the entries so that the table below fills cells A1:D17 in your worksheet.

Data
6sample mean=AVERAGE(A3:A8)
4sample size=COUNT(A3:A8)
2STDEV=STDEV(A3:A8)
1pre-Excel 2003 STDEV v1=SQRT((D4*SUMSQ(A3:A8) - (SUM(A3:A8)^2))/(D4*(D4 - 1)))
3pre-Excel 2003 STDEV v2=SQRT((SUMSQ(A3:A8) - (SUM(A3:A8)^2)/D4)/(D4 - 1))
5Excel 2003 and in later versions of Excel STDEV=SQRT(DEVSQ(A3:A8)/(D4 - 1))
Modified DataPower of 10 to add to data1
=A3 + 10^$D$10sample mean=AVERAGE(A12:A17)
=A4 + 10^$D$10sample size=COUNT(A12:A17)
=A5 + 10^$D$10STDEV=STDEV(A12:A17)
=A6 + 10^$D$10pre-Excel 2003 STDEV v1=SQRT((D13*SUMSQ(A12:A17) - (SUM(A12:A17)^2))/(D13*(D13 - 1)))
=A7 + 10^$D$10pre-Excel 2003 STDEV v2=SQRT((SUMSQ(A12:A17) - (SUM(A12:A17)^2)/D13)/(D13 - 1))
=A8 + 10^$D$10Excel 2003 and in later versions of Excel STDEV=SQRT(DEVSQ(A12:A17)/(D13 - 1))


Note After you paste this table into your new Excel worksheet, click the Paste Options button, and then click Match Destination Formatting. With the pasted range still selected, do one of the following:
  • In Microsoft Office Excel 2007, click the Home tab, click Format in the Cells group, and then click AutoFit Column Widths.
  • In Excel 2003, point to
    Column on the Format menu, and then click
    AutoFit Selection.
Cells A3:A8 contain 6 data points that are used in this example.

Cell D5 contains the value of STDEV for your current version of Excel. If you use Excel 2003 and later versions of Excel, this value should agree with the value in cell D8. The value in cell D8 shows the Excel 2003 and later versions of Excel value of STDEV (regardless of the version of Excel that you are using). Cells D6 and D7 show two approximations to the value of STDEV that were computed by earlier versions of Excel. The formula in cell D6 is the formula that is shown in the Help file for Excel 2002 and earlier.

In this example, all versions return the value 1.870828693. There are no computational problems here that cause differences in STDEV between versions of Excel.

You can use rows 10 to 17 to experiment with modified data by adding a constant (in this case a power of 10 is added) to each data point. It is well-known that adding a constant to each data point will not affect the value of sample standard deviation.

If you change the value in cell D10 (for example: to 1, 2, 3, 4, 5, 6, or 7), you can see the revised data values in cells A12:A17, and you can also see that all versions of STDEV are well-behaved in these 7 cases.

But, if you continue the experiment to try the values 8, 9, and 10 in cell D10, you will notice that the Excel 2003 and later versions of Excel value remains at 1.870828693 (as it should), while the Excel 2002 and earlier Excel version values change (even though they should remain constant at 1.870828693). This would not occur if computations could be done with infinite precision.

Earlier versions of Excel exhibit wrong answers in these cases because the effects of round-off errors are more profound with the computational formula that is used by these versions. Still, the cases used in this experiment can be viewed as rather extreme.

Results in Earlier Versions of Excel

In extreme cases where there are many significant digits in the data but, at the same time, a small variance, the old computational formula leads to inaccurate results. Earlier versions of Excel used a single pass through the data to compute the sum of squares of the data values, the sum of the data values, and the count of the data values (sample size). These quantities were then combined into the computational formula specified in the Help file in earlier versions of Excel.

Results in Excel 2003 and in later versions of Excel

The procedure used in Excel 2003 and in later versions of Excel uses a two-pass process through the data. First, the sum and count of the data values are computed and from these the sample mean (average) can be computed. Then, on the second pass, the squared difference between each data point and the sample mean is found and these squared differences are summed. In the numeric examples, even with a high power of 10 in cell D10, these squared differences are not affected and the results of the second pass are independent of the entry in cell D10. Therefore, the results in Excel 2003 and in later versions of Excel are more stable numerically.

Conclusions

Replacing a one-pass approach by a two-pass approach guarantees better numeric performance of STDEV in Excel 2003 and in later versions of Excel. Excel 2003 and in later versions of Excel results will never be less accurate than results in earlier versions.

In most practical examples, however, you are not likely to see a difference between Excel 2003 and later versions of Excel results and results in earlier versions of Excel. This issue occurs because typical data is unlikely to exhibit the kind of unusual behavior that this experiment illustrates. Numeric instability is most likely to appear in earlier versions of Excel when data contains a high number of significant digits combined with relatively little variation between data values.

If you use an earlier version of Excel and want to see if switching to Excel 2003 and later versions of Excel will make a difference, compare the results of
STDEV(values)
with the results of
SQRT(DEVSQ(values)/(COUNT(values) – 1))
If the results are consistent to level of accuracy that you want, then switching to Excel 2003 and later will not affect the value of STDEV.

If you use Excel 2003 and later versions of Excel, and want to see if the computed value of STDEV(values) has changed from the value that would have been found when you use an earlier version of Excel, compare
STDEV(values)
with
SQRT((SUMSQ(values) - (SUM(values)^2)/COUNT(values))/(COUNT(values) - 1))
This comparison gives at least a good approximation to the value of STDEV as found by earlier versions of Excel.

The procedure of finding the sum of squared deviations about a sample mean by
  • Finding the sample mean,
  • Computing each squared deviation,
  • Summing the squared deviations
is more accurate than the alternative procedure (frequently named the "calculator formula" because it was suitable for the use of a calculator on a small number of data points):
  • Find the sum of squares of all observations, the sample size, and the sum of all observations.
  • Compute the sum of 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 later versions of Excel by replacing this latter one-pass procedure by the two-pass procedure that finds the sample mean on the first pass and computes the sum of squared deviations about it on the second pass.

A short list of such functions includes VAR, VARP, STDEV, STDEVP, DVAR, DVARP, DSTDEV, DSTDEVP, FORECAST, SLOPE, INTERCEPT, PEARSON, RSQ, and STEYX. Similar improvements were made in each of the three Analysis of Variance tools in the Analysis ToolPak.
Propriedades

ID do Artigo: 826349 - Última Revisão: 19 de set de 2011 - Revisão: 1

Comentários