How not to plot zero's

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

Hi,
Wonder if anyone can help here.
I use the following formula to create a series for a graph
trend.
IF(ISERROR(A1/B1), "",(A1/B1))
Now if the data returns an error the cell looks blank.
I know the cell is strictly not blank.
When plotting this series on the graph the value turns to
zero.
This is what I want to change.
I don't want to plot the zero's, I know by using NA()
instead of "" returns #N/A which does not plot the zero
point but when this happens the series shows a continuous
line. I need to show the broken series.
In "tools, options, chart" the plot empty cells as: no
plotted (leave gaps) is selected.
Any help hints or queries would be of help.

Kind regards
Jim
 
The only thing in Excel that acts exactly like an empty cell is an empty
cell. You already understand the properties of the usual workarounds.
Since you not only want no plot symbol, but also want the connecting
line to break, you must delete the formula in that cell. For a dynamic
worksheet, you could write a worksheet change event that would delete or
restore formulas based on whether cells should be empty or not.

Jerry
 
Under the heading of "more than one way to skin a cat", you could
separately format that point and the point following to not display the
connecting line, though you may not like the effect on the plot legend.
Deleting the formula is simpler.

Jerry
 
Thanks.
Thought I'd have to do something like that. Hoping there
would be some way round it .
Jim
 
Back
Top