Show gaps does not work on charts

[dflak]

Here it is in its simplest form. I have values as #N/A and they should not plot , but they do even though I have display blank values as gaps. I've also tried the null string and get the same result. Excel 2013.

[Andy Pope]

#N/A only suppresses the data marker if the point is not the first or last data value. It does not create a gap.

Only way to create the gap is to have an empty cell.

[dflak]

#N/A! used to mean, "Don't plot this point - it does not exist." Apparently that's an "improvement" made after Excel 2007. I'm following instructions on this site: http://peltiertech.com/conditional-f...-excel-charts/ and #N/A! is supposed to show the gap.

I have confirmed that a totally blank cell (not a cell containing the null string) will produce the gap as desired. However, since no formula can produce an empty cell - the best you can do is the null string, then this task cannot be accomplished by formulas alone as it was in the past. It now must be done in VBA.

[Andy Pope]

You have never been able to break a line using a formula. The cell has to be empty and the setting for Hidden & Empty cells has to be gap.
=NA() only suppresses data marker.

=NA() works at the both ends of a line series but not in between valid data values.

[dflak]

Apparently so . I noticed that you chimed in on another post which I was working on where the person wanted red or green lines depending on where it was in relationship to another line. Your approach has merit. Basically, you have to create a set of new series based on the algebraic solution of where the lines intersect. This sounds like it would be an interesting issue to dig into deeper. It seems like a function worth developing. If I have spare time, I might do that.

Thanks for the advice.