Microsoft Knowledge Base

• Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0
• Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0

SYMPTOMS

In Microsoft Excel, the results you get when you use the STDEV() function will vary depending on the order in which you enter the values. This behavior is incorrect: the results of this function should not depend on the order in which you enter the values.

CAUSE

The process used to calculate the STDEV() involves the subtraction of two values that may be very similar to each other. This can result in a small rounding error due to the way a computer stores and manipulates numbers.

NOTE: The same inconsistent results occur if you use the formula on page 419 of the "Function Reference," version 4.0, to calculate this result manually.

WORKAROUND

To work around this problem, round the data when you calculate the standard deviation. To round the data, you can use a macro similar to the one in the following procedure.

Microsoft provides macro examples for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This macro is provided as is and Microsoft in no way guaranties that the following code can be used in all situations and will not support modifications of the code to suit specific customer requirements.

Macro That Rounds Data and Calculates the Standard Deviation

1. In a new macro sheet, enter the following macro code:

   A1: Rounded_STDEV A2: =RESULT(1) A3: =ARGUMENT("all_x",64) A4: =ROWS(all_x)*COLUMNS(all_x) A5: {=SUM(all_x^2)*A4-SUM(all_x)^2}

         NOTE: Enter this line as an array by pressing CTRL+SHIFT+ENTER
         (Windows) or COMMAND+ENTER (Macintosh).
   

   A6: =ROUND(A5,14) A7: =SQRT(A6/(A4*(A4-1))) A8: =RETURN(A7)

2. Select cell A1.

3. From the Formula menu, choose the Define Name command. Verify that Rounded_STDEV appears in the Name box, and A1 appears in the Refers To box.

4. Under Type, select the Function option and choose OK.

Explanation of Macro Code

   A1:   The name of the macro.
   A2:   Defines data type of returned value.
   A3:   Defines data type of input values.
   A4:   Calculates n.
   A5:   Formula to calculate the standard deviation.
   A6:   Removes small discrepancy by rounding.
   A7:   Formula to calculate the standard deviation.
   A8:   Returns correct result to worksheet.

MORE INFORMATION

Steps to Reproduce Behavior

1. On a new worksheet, enter the following values:

           A1: 0.09             B1: 0.09
           A2: 0.09             B2: 0.11
           A3: 0.11             B3: 0.12
           A4: 0.12             B4: 0.09
           A5: =STDEV(A1:A4)    B5: =STDEV(B1:B4)
   
   

2. Select cells A5 and B5.

3. From the Format menu, choose the Number command.

4. Select a Number Format of 0.00.

To use the macro that you created in the "Macro That Rounds Data and Calculates the Standard Deviation" section of this article, do the following:

• In cells A6 and B6 of the worksheet, enter the following:

        A6:=macro1!Rounded_STDEV(A1:A4)
  

        B6:=macro1!Rounded_STDEV(B1:B4)
  

  where macro1 is the name of the sheet on which you defined the macro.

REFERENCES

"Function Reference," version 4.0, page 419 "Function Reference," version 3.0, page 228 "Function and Macros," version 2.1, pages 109-110, 360