CHART Challenge: Plot NA# as null values

[xluser2007]

Hi All,

I have a chart challenge.

In the sample workbook, I have the following sample (made up) dataset.

x-axis y-axis 1 1234 2 3124 3 32 4 235 5 #N/A 6 #N/A 7 #N/A 8 #N/A 9 25

When line chart x-vs-y, in the workbook, It plots 1-4 correctly and then smooths a line through x values 5-9.

I would like it to not plot these NA# values at all and have a gap inbetween points 5-8 (inclusive).

If anybody could explain how to do this without manually altering the chart line, but by altering the chart settings or conditional formatting, I would be really grateful.

[lucas]

I don't have 2007. In 2003 I use ISNA to get around that problem.

I read this about 2007 however which might help with your problem:

In Excel 2007, the IFERROR function was introduced, which allows you
to specify a value to return if a formula returns an error. E.g,

[VBA]
=IFERROR(VLOOKUP(...),0)
[/VBA]

[Andy Pope]

reply I gave to a similar question on another forum.

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.

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.

[Paul_Hossler]

Might be close enough for you if can make the missing data blank or 0, instead of N/A

Select chart, Chart Tools, Design, Data Source, Hidden and Empty Cells, and select 'Show Empty Cells as Gaps"

Attached sample


Paul

[xluser2007]

Hi Paul and Andy and Steve,

First of sincere thanks for the kind and helpful replies.

I was unable to reply to you all as I travelled overseas and have had great difficulty in accessing the internet (I am writing this from a netcafe).

Andy, your solution is a very clever workaround for the problem, though difficult for me to implement due to insufficient columns in the spreadhseet template, though I may revisit this if i can modify the already built template.

Paul your solution sounds great, though the one issue is that I can't blank these cells (i.e. actually clear them) but can set them to "" or a 0 value as you suggested.

In both cases it appears to plot the "missing" values as 0 values with your solution, which is better than the current interpolation that is occurring, so kudos for suggesting this. If you do the same, do you see the values plotted as zeroes or genuine gaps?

Also, is there a way to have workbook event code to apply the setting you have suggested to every chart in the workbook that would work in Excel 2003 or Excel 2007?

Many thanks for your kind help.

[Paul_Hossler]

I used the option to leave as gaps

Paul

[jjstein]

Lucas,

I don't have 2007. In 2003 I use ISNA to get around that problem.

How?