not charting blank formula values as zero

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is is possible to not chart blank formula values as zeros. I have tried using tools, options, and chart (plot empty cells as 'not plotted'). This does not seem to work when the cell is referencing a formula. Thanks!
 
That's because cells with formulae aren't empty.

Have your formulae return the #N/A error instead - that will be
ignored in the chart. For instance:

=IF(A1<>0, B1/A1, NA())

You can use conditional formatting to change the font color to the
background color when the cell's value is #N/A if you don't want to
see the errors on your sheet.
 
Karen -

The cell that contains "" only looks empty to you and me, but Excel sees
the zero-length string. If you change "" to NA() in your formula, the
cell will now show the #N/A error, which looks ugly, but the chart
interpolates the points on either side of this error.

- Jon
 
Back
Top