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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
Back
Top