Plotting discontinuous data

[Metcalfe]

I am struggling to plot a simple scatter graph with discontinuous data.

I am able to add points but would like a discontinuous line which ignores blank data. I have tried the hidden cells method to no avail.

I have attached an example.

Thanks,

Steven

[MrShorty]

From what I have seen, what you want is not really quite possible in Excel. Excel can ignore data points in a scatter chart by using N/A, but it cannot seem to break the line over points like this. The only way I know of to get the effect you want is to have those "blanks" be truly blank. I would point out that what you call blank must not be truly blank in that =ISBLANK() returns FALSE, so that suggests that these are the result of some function. Even though some like Jon Peltier have been allegedly asking for years for some kind of NULL() or BLANK() function so that formulas can return something that will make these cells with formulas behave as if they were truly blank, MSFT has not yet seen fit to include such a function.

Andy Pope put this together that kind of does what you want: http://andypope.info/charts/brokenlines.htm The strategy is to create additional data series with "white" lines over the top of the portions of the line that you want to hide or "mask". I think the sheet was created in 2003 or earlier, because the new rendering in 2007+ does not quite hide the lines as well as I suspect it did under older versions. Perhaps that will be adequate for you.

If that is not good enough, then you will either need to truly delete/clear those cells, or make each continuous group of data a separate data series. As tedious as it will be, manually clearing those cells might be the easiest approach. If you want to explore options for automating one or both of these approaches, let us know.