not charting blank formula values as zero

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.


In article <974FC00E-A4FB-4006-B9E5-B1782A097D73@microsoft.com>,
"Karen M" <anonymous@discussions.microsoft.com> wrote:

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

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
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Karen M wrote:

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