Line Chart Question - break in line for blank cells

[JonPugh]

I have a line chart that I need to be able to provide a break in the line if there is a blank cell. At the moment it does not plot anything for the blank cell but it does draw the line from the last plotted cell to the next plotted cell after the blank.

Hope this makes sense. Anyone have any ideas without having to do it manually?

Thanks
Jonathan

[Cheeky Charlie]

#N/A values aren't plotted

either manually enter =na() or use it in a formula, like:
=if(A1=0,na(),A1/3)

[JonPugh]

Cheeky Charlie,

Thanks for replying. The problem is not that it is plotting the value (I am already using =NA()), it is that the line is still carrying on between the last plotted value and the one that appears after the NA cell.

Jonathan

[Lotus123]

Jonathan:

You will find differrent graphs do different things with #N/A. For example, bar and area graphs will "skip" the #N/A. Some graphs will substitute zero for #N/A.

For your sitatuon, since the line is not showing a break, I suspect you are using the basic standard line graph. It will not plot a point for #N/A, but it will draw from the prior point to the next. For this line chart you should use NULL (which is different than blank or "") instead of #N/A.

You may find if you simply delete or clear the entry from your graph then you will get a break in your line; however, there is no way, via formula, to return a NULL or empty cell (of which I am aware). For this reason I usually use area charts or bar graphs (which correctly use #N/A).

I have uploaded an example for your review.

[Cheeky Charlie]

Sorry, I didn't understand the problem straight away.
Google AndyPope.info for an example of how to deal with exactly this.

CC

[teylyn]

JonPugh, you need to tell Excel what to do with NA cells.

click the chart, go Tools - Options - Chart tab and select "plot empty cells as - not plotted" instead of "interpolated"

Interpolated will connect the datapoints, not plotted will leave gaps.

hth

[Andy Pope]

For a line #N/A will only remove the data marker.

The line will be interpolated for #N/A using the nearest real data values to the left and right. This means a line can be truncated by using #N/A but you can not create gaps. Only true empty cells will create gaps.
This over rides the settings of Leave Gaps, Interpolate and As Zero. Those settings only apply to empty cells.

[teylyn]

For a line #N/A will only remove the data marker.

The line will be interpolated for #N/A using the nearest real data values to the left and right. This means a line can be truncated by using #N/A but you can not create gaps. Only true empty cells will create gaps.
This over rides the settings of Leave Gaps, Interpolate and As Zero. Those settings only apply to empty cells.

Thanks for the clarification, Andy. One thing to mark on the "things I learned today" chalk board.

But to clarify: if the cell's value is determined by a formula, what would that formula have to return to simulate a truly empty cell in order to enable gaps in a line chart? Obviously not

=If(condition,value,NA())

and not

=IF(condition,value,"")

Any tricks up your sleeve?

cheers

[Andy Pope]

There in lies the problem. There is nothing that can be returned to say "ISEMPTY". It's a feature we have asked MS to provide.

If your data is not too complicated you can try
http://www.andypope.info/charts/brokenlines.htm

Otherwise VBA to clear the cells is the only other option.

Or I guess you could use multiple series to plot the segments.

[teylyn]

It's a feature we have asked MS to provide.

That explains it! Thank you. Especially for

http://www.andypope.info/charts/brokenlines.htm

Time to study the masters and learn!

have a good day!

[Cheeky Charlie]

^Yah that's the link I was too lazy to find!

[JonPugh]

A bit late (been on leave) but thanks to everyone who contributed to this thread.

Jonathan