I can't get my graph to interpolate my missing data

[Sean]

I used the Tools->Options->Chart tab menu to get excel to interpolate my
empty cells in the active chart. I also clicked the option to leave gaps but
the graph still sends lines down to zero where I have gaps.

 

[Ragdyer]

XL graphs should ignore cells that equate to the #N/A error.

If you have formulas in these "empty" cells, for instance:
=IF(A1+A2>0,A1+A2,"")
Revise them to something like this:
=IF(A1+A2>0,A1+A2,NA())

If the values are keyed in, when a cell has no value, enter:
=NA()

 

[Jay Somerset]

I used the Tools->Options->Chart tab menu to get excel to interpolate my
empty cells in the active chart. I also clicked the option to leave gaps but
the graph still sends lines down to zero where I have gaps.

To eliminate the lines that dive down to zero, replace the missing values
with #N/A. Excel ignores these values when charting.

 

[Richard Craigie]

But the problem then is you can't use functions like SUM, AVERAGE, etc with
cells containing #NA.

 

[David Biddulph]

.... so the sensible thing is to put the NA() into the range you want to
plot, but not into ranges that you use for further calculation.