XL5: Error Bars Too Large When Standard Error Used

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