Chart data with zeroes

[Code Numpty]

I have used an IF formula to replace blanks, zeroes etc. with #N/A as I
thought charts ignored this. My source data is 2 columns as below displaying
as a line chart:

#N/A #N/A
0.20 0.0017497
0.40 0.0017613
0.60 0.0017538
0.80 0.0017459
1.00 0.0017397
1.20 0.0017358
1.60 0.0019809
2.00 0.0021619
#N/A #N/A
#N/A #N/A
#N/A #N/A

The #N/As are plotted as zeroes with #N/A displaying instead of a figure on
the X axis.

I need to get rid of the #N/As displaying on the X axis and have excel
ignore them on the chart. Any help greatly appreciated.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...e8cae4a1f4&dg=microsoft.public.excel.charting

 

[Andy Pope]

Hi,

The use of NA() within a line chart will only stop the data marker being
plotted.
The line will be interpolated between valid data points. The only way to
truly break the line is to have empty data cells.

You could use Autofilter to hide rows containing #N/A. And if the chart has
the option to Plot visible cells only the information will not be displayed.

Cheers
Andy

 

[Code Numpty]

Thanks Andy,

I tried replacing "#N/A" in the IF formula with "" but this still plots
'zeroes'.

My problem is that this is to appear in a template where there will be data
within the chart source data but not necessarily in every cell. I am trying
to avoid the user having to do anything to produce the chart and cannot think
of a way round this.

 

[Andy Pope]

The formula replacement would be

=IF(<test>,<value>,NA() )

Cheers
Andy

 

[Code Numpty]

I may have misunderstood.
Same result #N/A appears on the x axis and point at zero.

 

[Andy Pope]

I doubt you miss-understood

Even using NA() in the formula you will not get a different result.
The Category label will still appear as #N/A. Only way to get rid of the
category is to exclude it from the data series. Either by not selecting that
cell or hiding the row.

The line will still appear with no break in it.
The data marker, if present, will not be displayed.
The data label will not be shown if Value is the item being displayed. If
you are displaying Category then the #N/A will appear.

There is not a formula based way of removing data points from a chart.

Cheers
Andy

 

[Code Numpty]

Thanks Andy, I'm going to have to work my way round this by getting the user
to paste values into another area where it is already set up as the source
data. This way at least I will get truly blank cells for the chart to ignore
(I hope!).

Your help has been appreciated. Forums are the best part of the web.

 

[Beth@ngc]

Thanks Andy, I'm going to have to work my way round this by getting the user
to paste values into another area where it is already set up as the source
data. This way at least I will get truly blank cells for the chart to ignore
(I hope!).

Your help has been appreciated. Forums are the best part of the web.

I don't know if this is the same problem you're having but mine is that I am
trying to create a chart from data that is in the form of a formula,
some of those cells have a formula that returns a blank cell, "". I want my
line chart to stop if the cell is blank, instead it is showing as a zero
value.
Any ideas on how to do this.

 

[Andy Pope]

Hi,

It probably is.
You need to do this revision to your formula structure.

=IF(<test>,<value>,NA() )

NA() instead of "".

But as previously stated this will not cause a break in the line only
stop the data marker appearing and interpolation of the line between
valid points.

Cheers
Andy