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/comm...excel.charting

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

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Code Numpty" wrote in message
...
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/comm...excel.charting

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" wrote:

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

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Code Numpty" wrote in message
...
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/comm...excel.charting

The formula replacement would be

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

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Code Numpty" wrote in message
...
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" wrote:

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

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Code Numpty" wrote in message
...
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/comm...excel.charting

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


"Andy Pope" wrote:

The formula replacement would be

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

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Code Numpty" wrote in message
...
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" wrote:

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

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Code Numpty" wrote in message
...
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/comm...excel.charting

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
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Code Numpty" wrote in message
...

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


"Andy Pope" wrote:

The formula replacement would be

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

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Code Numpty" wrote in message
...
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" wrote:

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

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Code Numpty" wrote in message
...
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/comm...excel.charting