Broken line in line graph when formula is NA

[jasoncyrus]

Hello,

I am using excel 2010.

What I want to do is show a dotted or broken line to automatically be displayed in my line chart if the formula in the cell is NA. So I might have something like 100%, 90%, 95%, NA, 100%.

It would be great if there was a regular line and then from 95% to NA to 100%, there would be a broken line to show that there is no data for that month.

Any help is much appreciated.

 

[mole999]

you could create a second data line that you would show in the background, so that the dotted line is modelled in between the missing values

 

[Jon Peltier]

Problem is, the NA() doesn't leave a gap, it ends up with a line connecting the points on either side.

This whole thing, with #N/A, and blank cells and non-blank cells that look blank, and gaps in the chart, is all too complicated and misunderstood. I wrote a tutorial on my blog, Mind the Gap – Charting Empty Cells, and I hosted a follow-up post, Another approach to plotting gaps in Excel charts. You could read these to get a sense of the problem and maybe find a workaround.

Then once you figure it out, you can combine the approach that leaves a gap with mole999's suggestion to plot a second series first with the dotted line you want to appear where the gap is.

 

[jasoncyrus]

Re: Broken line in line graph when formula is NA:

Thank you both of you. This is really helpful.