Line Chart Question - break in line for blank cells

Originally Posted by Andy Pope

For a line #N/A will only remove the data marker.

The line will be interpolated for #N/A using the nearest real data values to the left and right. This means a line can be truncated by using #N/A but you can not create gaps. Only true empty cells will create gaps.
This over rides the settings of Leave Gaps, Interpolate and As Zero. Those settings only apply to empty cells.

Thanks for the clarification, Andy. One thing to mark on the "things I learned today" chalk board.

But to clarify: if the cell's value is determined by a formula, what would that formula have to return to simulate a truly empty cell in order to enable gaps in a line chart? Obviously not

=If(condition,value,NA())

and not

=IF(condition,value,"")

Any tricks up your sleeve?

cheers

There in lies the problem. There is nothing that can be returned to say "ISEMPTY". It's a feature we have asked MS to provide.

If your data is not too complicated you can try
http://www.andypope.info/charts/brokenlines.htm

Otherwise VBA to clear the cells is the only other option.

Or I guess you could use multiple series to plot the segments.