XL5: Error Bars Too Large When Standard Error Used

Last reviewed: September 12, 1996
Article ID: Q113855
The information in this article applies to:

- Microsoft Excel for Windows, version 5.0

SYMPTOMS

In Microsoft Excel version 5.0 for Windows, if you attach error bars to a series in a chart, the bars that are attached may be too large. This will occur if you attach bars that use the Standard Error value.

CAUSE

This error occurs because Microsoft Excel is using an incorrect method for calculating the standard error of the data points in the series.

WORKAROUND

It is possible to calculate the correct standard error value and make the error bars in the chart use this correct value, rather than the incorrect value.

These steps assume that the series to which error bars should be attached is referring to the range Sheet1!$A$1:$A$10.

  1. In an empty cell (in this example, the cell is assumed to be Sheet1!$A$11), enter the following formula:

          =STDEV(Sheet1!$A$1:$A$10)/SQRT(ROWS(Sheet1!$A$1:$A$10))
    

    The number generated by this formula is the Standard Error for the range Sheet1!$A$1:$A$10.

  2. In the chart, select the series to which error bars should be attached.

  3. From the Insert menu, choose Error Bars.

  4. In the Error Bars dialog box, set the following values:

       Display                    : Choose the type you want to use
       Error Amount option button : Selected
       '+' edit box               : =Sheet1!$A$11
       '-' edit box               : =Sheet1!$A$11
    
    

  5. Choose OK to accept the change.

The series should now display error bars of the correct height.


KBCategory: kbusage
KBSubcategory: xlwin

Additional reference words: 5.00



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: September 12, 1996
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.