Plotting discontinuous data in Excel

Factual Questions
1

I am creating an Excel chart, and for some X values there is not a Y value. The Y values are determined by formula, and if the data is not applicable for that the point the formula returns the null string. For example,

=IF(AND($B8>=J$1,$B8<K$1),$C8,"")

If you have a cell with no data in an Excel chart, Excel graphs it as a discontinuity. However, if you have a cell with a formula that returns the null string, Excel plots it as zero. Same thing if you return a blank. So instead of a chart with a line with missing sections, I get a sawtooth line that keeps dropping to zero and bouncing back.

Is there any result that a formula can return that will cause Excel to ignore the cell and treat it as a discontinuity?

2

I saw this exact question in a magazine this week. Excel will plot nothing when you use “” for a vaule giving you a broken line, and if you leave it blank, it will plot 0. If you use the function =NA() then the cell will have the value of #N/A. Excel ‘ignores’ those cells when plotting and will give you a continous line.

So, your function sould look like this:

=IF(AND($B8>=J$1,$B8<K$1),$C8,=NA())

Note the bolded section.

Hope this solves your problems.

3

Thank you! :slight_smile: Isn’t that great when someone asks a question that you just happened to read the answer for recently? This makes the data look kind of ugly, but that’s OK because I just care about the chart.

I had to make one correction, which was to drop the equals sign. If you just type that into a cell you need the equals sign, but when using it as the result of a formula you don’t.

4

Duh, I knew that. :smack: I was just cut-and-pasting away there. :slight_smile: Glad I could help.