Microsoft KB Archive/104951

Microsoft Knowledge Base

Excel: STDEV() Function Results Vary with Order of Numbers

Last reviewed: June 30, 1997
Article ID: Q104951

The information in this article applies to:

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

Cell A5 displays a value of 0.02, and cell B5 displays a value of 0.01.

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.

Cells A6 and B6 will display the number 0.02, the correct standard deviation for the values.

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

Additional reference words: 4.00 4.00a M_eXceL Keywords : kbprg SynFnc Version : 2.X 3.00 4.00 4.00a 5.00| 3.00 4 Platform : MACINTOSH WINDOWS THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Last reviewed: June 30, 1997 ©1997 Microsoft Corporation. All rights reserved. Legal Notices.