Supressing zeros on charts (Excel 2000)

Topic

New Reply

Does anybody know how to suppress zero values on a line graph, so that the graph does not post a value ?

When the source cell is blank, the line chart does not post a value (desired outcome).

BUT, if there is a formula in the source cell, then the chart plots a zero value.

I have tried FORMATTING the source cell, and IF statements in the formulas to produce blank cells, but it seems that so long as there is any underlying formula in the cell, then the chart still plots the value as a ZERO, not a BLANK.

Is there an easy solution ?

Thanks

David Gazzola, London
+44 771 570 4815

Reply | Quote

Replies

Use a different formula that returns #N/A instead of a 0 or “” like in:

=IF(A1,do-this,#N/A)

Aladin

Reply | Quote

Hi,
Depending on your situation, you may be able to use a defined name as the source data for your chart. If for example, your formulae are in A1:A4 and you only want to plot the ones that show a value, you could define a name as =OFFSET($A$1,0,0,count($A:$A),1). If you called this name PlotArea you could then define your data series as =PlotArea and it should only plot the visible values.
Hope that helps.

Reply | Quote

To set how Excel deals with missing data, select your chart and choose Tools, Options. In the Options dialog box, click the Chart tab and then select the appropriate option. Your choice will apply to all data series in the selected chart. That means, that you need to have a chart selected before you can select the options.

You can also represent data with the formula =NA() instead of leaving a cell blank. The chart will use interpolation for data cells that contain this formula, regardless of the setting in the Options dialog box.

Reply | Quote