Microsoft KB Archive/114519

= Microsoft Knowledge Base =

XL5: Error Bars Do Not Display If Blanks in Data Range
Last reviewed: September 12, 1996

Article ID: Q114519

The information in this article applies to:


 * Microsoft Excel for Windows, version 5.0
 * Microsoft Excel for the Macintosh, version 5.0

SYMPTOMS
In Microsoft Excel, if you add error bars to a data series on a chart, and you use the Custom option in the Error Bars dialog box, the following behavior occurs:


 * If the data on the worksheet that the chart series is created from contains a blank cell or a cell with the value #N/A, the error bar for this data point does not appear, and the error bars for each of the following data points in the series either do not appear on the chart or appear incorrectly.
 * If the Custom + or - box contains a worksheet range, and a cell in the range is blank, then the error bar for this data point and the error bars for each of the following data points do not appear on the chart.
 * If the Custom + or - box contains a worksheet range, and a cell in the range contains the value #N/A, then the error bar for this data point does not appear on the chart, and the error bars for each of the following data points are incorrect.

STATUS
Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

Workaround 1
NOTE: This behavior depends on the option selected under Empty Cells Plotted As on the Chart tab of the Options dialog box. If the Not Plotted (leave gaps) option is selected, the error bars for each of the data points following the data point created from the blank or #N/A cell value do not appear. If the Zero option is selected, the error bars for each of the data points following this cell data point appear incorrectly.

To avoid this behavior when the worksheet that the chart series is created from contains a blank cell, do either of the following:

 Instead of using a blank cell, enter a 0 in the cell for which you do not want a data point to appear on the chart. -or- Use any of the other error amount options in the Error Bars dialog box to display error bars on the chart.

Workaround 2
To prevent an error bar from displaying for a data point on your chart, enter 0 as the error amount value for that data point using either of the following methods:

 In the Custom + or - box in the Error Bars dialog box for the data series, enter the error amount values for the data points as an array, for example {1,2,0,4,2}, using a 0 for the data point for which you don't want to display an error bar. -or- In the Custom + or - box in the Error Bars dialog box for the data series, enter the worksheet range as the error amount values, where the cell for the data point for which you don't want to display an error bar contains the value 0.

MORE INFORMATION
If the error value is a fixed value or can be expressed as a percentage, then you can enter the value in the Error Bars dialog box in the Fixed Value box or the Percentage box. When you enter a value in the Fixed Value box or the Percentage box, an error bar of this value displays for all of the data points in the selected series. You can add an error bar to a data series on an area, bar, column, line, or xy (scatter) chart.