XL5: Error Bars Too Large When Standard Error Used

ID: Q113855

5.00 WINDOWS kbusage

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

Keywords          : xlwin 
Version           : 5.00
Platform          : WINDOWS

Last Reviewed: September 15, 1996