How do I stop excel graphing missing (#N/A) data?

[Guest]

I am trying to create a line graph with a series of data that contains #N/A "values" (which is generated through a formula)
eg
Month: Jan Feb Mar Apr Ma
Weight: 3 6 9 #N/A

Problem is excel will create a line between the March and May figures rather than leaving a gap in the line. Otherwise it then looks like I have the April data which I don't....argh!!
I can change the formulas to produce something other that #N/A, eg "" but this causes the line to dip to zero
Any help most appreciated

 

[Jerry W. Lewis]

Excel has no true missing value. Text plots as zero. #N/A does not
plot, but also does not break the connecting line, as you have found.
The only thing that behaves exactly like an empty cell is an empty cell.
Delete the formula and the line will break. If the data is dynamic,
you might right a change event macro that will insert or remove formulas
depending on whether the result is numeric or not.

Jerry

 

[Jon Peltier]

You might find it helpful to use a distinct marker for the real data
points. Perhaps make the marker larger than the default. This way, the
absence of a marker in April is more pronounced.

- Jon