Making break in excel line, not giving zeros

[cpc34]

I am plotting a graph of x vs y, where the y value contains an IF statement. It tests whether a certain value is valid, and if it is not I have it displaying "Invalid". My output is therefore a series of values with a few "invalid" cells.
My problem is that when I plot this, Excel assigns these "Invalid" cells the value zero. I would much rather it gave me a break in the line where the results are not valid, much as if I had deleted the cell manually. Note, it is not always the same cells that have the "Invalid" value, so I need something that can be applied to all the y-values.

Actually, the same goes for #DIV/0! values. I'd rather they didn't plot at all, rather than being given a zero value, but maybe I'll leave that for another time.

 

[GlennUK]

Replace invalid with NA() and see what happens.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[cpc34]

Thanks. That's better now. It now ignores the invalid values, but interpolates a straight line instead. Different question, maybe, but can the colour/style of part of the line be changed to show that it is Invalid. Ideally, I'd like it to remove that section of the line altogether, but I'm open to suggestions.

 

[mintjulep]

Tools | Options | Charts ...

Uncheck Plot empty cells as zero.

You might need to chage your if to leave a null "" instead of invalid or NA().