how to make gaps in plotted data when cell has formula

[D. Eglen]

I have a simple data set with a value for each month except June.

I want the chart to reflect a gap for June, not a zero value. Although I
did tools, options, chart, make empty cells plot as gaps, it still plots
the June value as a zero.

In an attempt to get rid of div/o error, I have a formula in the data set
as If (b5=0, "", c5/b5) . What I'm plotting is the quotient c/b.

We have determined that the formula is what is causing the problem.
Interestingly, if I open this same worksheet (as is) in quattro pro, the gap
appears in the chart.

Is there a different way to do this? thanks.

 

[Tushar Mehta]

Yes. Replace the "" by NA()

If this messes up subsequent formulas, use a dummy range for plotting
purposes.

If you don't like the resulting #N/A display, see Debra Dalgleish's
http://www.contextures.com/xlCondFormat03.html

--
Regards,

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