How not to plot zero's

[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

 

[Jerry W. Lewis]

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

 

[Jerry W. Lewis]

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

 

[Guest]

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

 

[Andy Pope]

Hi,

These examples may help.

http://www.tushar-mehta.com/excel/software/na_discontinuity/index.html
http://www.andypope.info/charts/brokenlines.htm

Cheers
Andy

 

[Guest]

I have exactly the same question.
If you have any joy, can you let me know.
Thanks

 

[Tushar Mehta]

If you use NA() instead of "", if you are plotting a XY Scatter chart,
and if you are willing to use a programmatic solution, check

Chart gap for N/A
http://www.tushar-mehta.com/excel/software/na_discontinuity/index.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions