How to make #N/A cells behave as if they were empty cells

[vsoler]

Using Excel 2007

To make it simple, say that in A1:A5 I have one single formula that
returns

1
2
#N/A
4
5

I need to plot a line chart made of 2 pieces, one going from 1 to 2,
the other one going from 4 to 5. I want to see 2 lines, but I only
have one series of data.

If I delete the formula in A3 (that returns #N/A) it works fine.
However, I cannot delete the formula and I see one single line.

If my formula returns an empty string ("") or a blank string (" ") it
gets no better.

Is there anything I can do?

 

[Jon Peltier]

Vicente -

There's no way to mimic a truly blank cell with a formula. We've been
asking for years. There are a number of ways to get around the issue.

A VBA solution might delete cells with N/A, then reinstate the formulas
when the data changed.

Andy Pope (http://andypope.info) has a technique to add another series
that draws a white line over the line that spans the N/A in the first
series.

An alternative might generate two series, one before the N/A and one
after. This could be extended to a number of series, for each segment
between N/As. This is simplified by the fact that any N/As at the end of
a series do not generate a connecting line.

- Jon