Chart data with zeroes

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/community/en-us/default.mspx?mid=15591f70-f56e-4450-96c9-2ae8cae4a1f4&dg=microsoft.public.excel.charting
0
11/17/2008 11:08:07 AM
excel.charting 18370 articles. 0 followers. Follow

8 Replies
703 Views

Similar Articles

[PageSpeed] 55

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" <CodeNumpty@discussions.microsoft.com> wrote in message 
news:15591F70-F56E-4450-96C9-2AE8CAE4A1F4@microsoft.com...
>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/community/en-us/default.mspx?mid=15591f70-f56e-4450-96c9-2ae8cae4a1f4&dg=microsoft.public.excel.charting 

0
andy9699 (3616)
11/17/2008 11:14:15 AM
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" <CodeNumpty@discussions.microsoft.com> wrote in message 
> news:15591F70-F56E-4450-96C9-2AE8CAE4A1F4@microsoft.com...
> >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/community/en-us/default.mspx?mid=15591f70-f56e-4450-96c9-2ae8cae4a1f4&dg=microsoft.public.excel.charting 
> 
> 
0
11/17/2008 11:42:02 AM
The formula replacement would be

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

Cheers
Andy
-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Code Numpty" <CodeNumpty@discussions.microsoft.com> wrote in message 
news:84A507CA-0212-4D94-A3D3-78BDE9EEEAEC@microsoft.com...
> 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" <CodeNumpty@discussions.microsoft.com> wrote in message
>> news:15591F70-F56E-4450-96C9-2AE8CAE4A1F4@microsoft.com...
>> >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/community/en-us/default.mspx?mid=15591f70-f56e-4450-96c9-2ae8cae4a1f4&dg=microsoft.public.excel.charting
>>
>> 

0
andy9699 (3616)
11/17/2008 11:59:45 AM
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" <CodeNumpty@discussions.microsoft.com> wrote in message 
> news:84A507CA-0212-4D94-A3D3-78BDE9EEEAEC@microsoft.com...
> > 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" <CodeNumpty@discussions.microsoft.com> wrote in message
> >> news:15591F70-F56E-4450-96C9-2AE8CAE4A1F4@microsoft.com...
> >> >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/community/en-us/default.mspx?mid=15591f70-f56e-4450-96c9-2ae8cae4a1f4&dg=microsoft.public.excel.charting
> >>
> >> 
> 
> 
0
11/17/2008 1:03:01 PM
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" <CodeNumpty@discussions.microsoft.com> wrote in message 
news:AF081598-3F1C-4A32-AFA0-3FE4DE0CA000@microsoft.com...
>
> 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" <CodeNumpty@discussions.microsoft.com> wrote in message
>> news:84A507CA-0212-4D94-A3D3-78BDE9EEEAEC@microsoft.com...
>> > 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" <CodeNumpty@discussions.microsoft.com> wrote in message
>> >> news:15591F70-F56E-4450-96C9-2AE8CAE4A1F4@microsoft.com...
>> >> >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/community/en-us/default.mspx?mid=15591f70-f56e-4450-96c9-2ae8cae4a1f4&dg=microsoft.public.excel.charting
>> >>
>> >>
>>
>> 

0
andy9699 (3616)
11/17/2008 1:12:12 PM
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.

"Andy Pope" wrote:

> 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" <CodeNumpty@discussions.microsoft.com> wrote in message 
> news:AF081598-3F1C-4A32-AFA0-3FE4DE0CA000@microsoft.com...
> >
> > 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" <CodeNumpty@discussions.microsoft.com> wrote in message
> >> news:84A507CA-0212-4D94-A3D3-78BDE9EEEAEC@microsoft.com...
> >> > 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" <CodeNumpty@discussions.microsoft.com> wrote in message
> >> >> news:15591F70-F56E-4450-96C9-2AE8CAE4A1F4@microsoft.com...
> >> >> >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/community/en-us/default.mspx?mid=15591f70-f56e-4450-96c9-2ae8cae4a1f4&dg=microsoft.public.excel.charting
> >> >>
> >> >>
> >>
> >> 
> 
> 
0
11/17/2008 5:56:10 PM

"Code Numpty" wrote:

> 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.
> 
> "Andy Pope" wrote:
> 
> > 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" <CodeNumpty@discussions.microsoft.com> wrote in message 
> > news:AF081598-3F1C-4A32-AFA0-3FE4DE0CA000@microsoft.com...
> > >
> > > 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" <CodeNumpty@discussions.microsoft.com> wrote in message
> > >> news:84A507CA-0212-4D94-A3D3-78BDE9EEEAEC@microsoft.com...
> > >> > 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" <CodeNumpty@discussions.microsoft.com> wrote in message
> > >> >> news:15591F70-F56E-4450-96C9-2AE8CAE4A1F4@microsoft.com...
> > >> >> >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/community/en-us/default.mspx?mid=15591f70-f56e-4450-96c9-2ae8cae4a1f4&dg=microsoft.public.excel.charting
> > >> >>
> > >> >>
> > >>
> > >> 
> > 
> > 

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. 

0
ngc (3)
11/24/2008 6:28:04 PM
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

Beth@ngc wrote:
> 
> "Code Numpty" wrote:
> 
> 
>>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.
>>
>>"Andy Pope" wrote:
>>
>>
>>>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" <CodeNumpty@discussions.microsoft.com> wrote in message 
>>>news:AF081598-3F1C-4A32-AFA0-3FE4DE0CA000@microsoft.com...
>>>
>>>>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" <CodeNumpty@discussions.microsoft.com> wrote in message
>>>>>news:84A507CA-0212-4D94-A3D3-78BDE9EEEAEC@microsoft.com...
>>>>>
>>>>>>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" <CodeNumpty@discussions.microsoft.com> wrote in message
>>>>>>>news:15591F70-F56E-4450-96C9-2AE8CAE4A1F4@microsoft.com...
>>>>>>>
>>>>>>>>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/community/en-us/default.mspx?mid=15591f70-f56e-4450-96c9-2ae8cae4a1f4&dg=microsoft.public.excel.charting
>>>>>>>
>>>>>>>
>>>>>
>>>
> 
> 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, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
11/24/2008 7:58:40 PM
Reply:

Similar Artilces:

Remote data access
As a new .net developer, I would like to know how a VB.net Windows application can access a SQL Server database residing on a web server. In other words, using the Visual Studio IDE, is there a way to develop a Visual Basic, Windows application that can access a SQL Server database over the internet. Thanks for suggestions, John C. John C. wrote: > As a new .net developer, I would like to know how a > VB.net Windows application can access a SQL Server > database residing on a web server. > > In other words, using the Visual Studio IDE, is there a > way to develop...

Data Validation #12
Hi. I have data validations in sheet1 as named range lists from sheet2. When I have deleted sheet2 and copy sheet2 from identical other workbook - my validations does is not work... I see Name ranges in copied worksheet, but when I use they in validation - I have error: "The Source currently evaluates an error. Do you wish continue?" Only after deleting of Named ranges in Sheet2 and recreating its, my validations works. It is possible after replacing of worksheet with named ranges for validations (named ranges names are identical) use copied worksheet without recreating ...

Data Entry Form 03-20-08
Hi, I created a form to use for viewing currently records and entering new data. This time, the form doesn't allow me to enter new data. In fact, all text boxes didn't let me to enter new information. Please help Thanks Chi Several things to check: In form properties: Allow Additions = Yes Allow Edits = Yes If the form's record source is a query, be sure the query is updatable. -- Dave Hargis, Microsoft Access MVP "Chi" wrote: > Hi, > > I created a form to use for viewing currently records and entering new data. > > This time, the ...

Sorting lots of data
We are trying to sort through a csv file that we converted to an excel spreadsheet. the spreadsheet has over 40,000 entries which include mailing addresses that are in some cases duplicated. is there anyway to sort/filter the info to locate the duplicates and then remove them?? any help would be greatly appreciated. thank you for your responses. Hi use 'Data - Advanced Filters' and check 'unique entries' or use the following addin for this: http://www.rondebruin.nl/easyfilter.htm -- Regards Frank Kabel Frankfurt, Germany miss lynar wrote: > We are trying to sort thr...

Stacking Data??
if I have data like this A B C D... 1 Type\Date 01/10/04 02/10/04 03/10/04... 2 T-X 100 50 100 3 T-Y 50 70 0 4 T-Z 400 0 400 ... .. ...

charting independent XYScatter graphs
I'm trying to create a XYScatter graph of 3 independent datasets from within VB6 using OLE/Excel2000. I'm using the KB147803 article as a basis, but am stuck and hope someone can help. The example in KB147803 seems to indicate that there can only be one set of X-Values for the 3 XY Scatter Graph data sets. The X-Value series is the first row of data in the sheet, and is called out as a set of Category labels ('cwCategoryLabels=1'), and applies to all three subsequent rows of Y-Value data. In my case, I want to plot 3 independent sets of XY data, so I'd like to pr...

Help with graph / chart
I have a graph for weeks 1-52, I have split this into 4 seperate graphs each showing a quarter (13 weeks) I cant remember exactly how I created them but possibly using some sort of copy paste as each chart show weeks 1 - 13 along the bottom. This should read......... for chart 1 1-13 for chart 2 14 - 26 chart 3 27 - 40 chart 4 41 - 52 How do I change this on each chart to read the week numbers indicated.? thanks Hi, You need to define the Category labels for the chart. Chart 1 is fine as it defaults to the values 1 to 13. For the other 3 charts you will need to create...

Don't plot zero values
I am currently using Excel XP. I have a scatter chart and would only like to plot non zero values. These are not null values. Is there a chart setting I can do to skip plotting zero values or do I need to somehow filter/sort the data first and then plot? TIA George Hey George - If there are true zeros in the data, perhaps the easiest way to exclude them from the chart is to use an autofilter on the data that hides the rows with zeros. Alternatively, you could insert a column to hide the zeros. Say the range with zeros is in B1:B10. Select C1:C10, and enter this formula into C1: =IF...

Copy chart with reference table
I have a pie chart neatly tied to a data table alongside. Now I want to copy both the chart and table to a different area of the sheet so I can modify the new data table and have 2 different charts. My issue is that the chart will only allow absolute references, so when I copy both the chart and table together the new chart still is tied to the old table - so I have to go and change the source data ranges one by one. Any way that I can do this easily? Copy the whole sheet. The copied chart links to the data on the copied sheet. Now cut the range that includes the copied char...

order data by a rank
i need to create a form order by the militaty rank.This form is based in a query "qrystaff" with three fields: MemberID FullName and Rank.I´d like that the data were ordered firstly by MilitaryRank and secondly by FullName in alphabetical order.There is an easy way to do this? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1 When you create queries, you're given the option to specify the sort order under each field. If the fields aren't in the order from left-to-right in the query grid, you can add ...

Turn data #2
Is there a way to turn the data in an Excel spreadsheet so that what is on the top is now on the side and what is on the side is now on top? I need to export my spreadsheet into Access but, I can't get it to format correctly in its current orientation. ~Make a backup copy of your data to work from (just in case, so you don't lose data) ~Highlight the entire range of data; click >Edit >Copy ~Choose a new location, on a different sheet perhaps; click >Edit >Paste Special and select the Transpose box; click OK Does that do it for you? thank you so much. You are a genius...

Data in columns not rows
I have file that is names, addresses and phone numbers. When I copy it into excel it comes out like this. (COLUMNA) NAME ADDRESS ADDRESS2 PHONE I need it to be like this COLUMNA COLUMNB COLUMNC COLUMND NAME ADDRESS ADDRESS2 PHONE Is there a way to change this without copying and pasting? Jenn Is the data consistently 4 rows? Or do you have varying sets? How far down Column A do the sets extend? If 4 rows per set try this..... In B1 enter =INDEX($A$1:$A$3000,(ROW()-1)*4+COLUMN()-1,1) Drag/copy across to E1 then select B1:E1 and drag/copy down u...

Using a Text / Data output as a cell reference
I am trying to use the end of a column as a divisor and need to convert what i guess is text into an actual cell reference if possible. In column A, say there are 13 data points ending at cell A13. I then try to turn that into a cell reference with the formula ="A"&TEXT(COUNTA(A1:A13,),"0") I put this formula in B1 the output of this is then A13 what i need this to do, is be able to divide any cell by A13. The reason I am going through all this trouble is that there are many columns and each has a different # of data points. I hope this is somewhat clear. Thanks ...

XY Chart
I have created a short VBA macro that reads x and y data from a spreadsheet, where every row represents a new series. Besides the x and y data colums, I also have columns that specify the size of the marker, the markerstyle, and the foreground and background colors. I have adapted some of John Walkenbach's code to label each of the datapoints using .name. The x axis is basically an integer 1 to 5 and the y axis is a value like price or variable margin. There usually are multiple datapoints on any given integer on the x axis. For example, I may be plotting the price that a custom...

Display of CRM data in SharePoint
Is is possible to allow non-CRM users the ability to view CRM data on SharePoint without using the CRM external connector? Thanks, -Rick M. Microsoft would require you to have a license per user I think ============================== John O'Donnell Microsoft CRM MVP http://www.crowecrm.com "Rick M" <RickM@discussions.microsoft.com> wrote in message news:B3E731A1-D059-4D49-B879-6025A1817FD8@microsoft.com... > Is is possible to allow non-CRM users the ability to view CRM data on > SharePoint without using the CRM external connector? > > Thanks, > -Rick...

Outlook Data File Not Closing Properly
I always get the following error message when starting Outlook 2002 (running Office XP and Windows XP) : "The data file 'outlook' was not closed properly. The file is being checked for problems." I get this message everytime (whether I hibernate or shut down) when I start Outlook. This checking of the file can take anywhere from 30 seconds to 5 minutes and is very frustrating. Anyone have any idea what the issue is ? This started happening just after I installed XP SP3. ...

BP Installation Error
Someone post this question before:- MbfPackager data importation process has failed. please c:\DOCUME~1\ADMIN~1\LOCALS~1\Temp\MbfPackager.Import.NoMerge.Common.xml for more information. Problem: When installing Microsoft Business Portal 2.5. Can anyone help me on this error? I'm installing Business Portal 2.5 Feature Pack on Windows 2003 Standard Edition. And, the BP is reading the Great Plains 8.0 database at another server. Cheers, Emily ...

chart
Help... Is is possible to insert a header and/or footer into multiple charts(Excel 2000 or 2002)? I was hoping I could insert a common chart header/footer as is possible with worksheets. Any help appreciated. You could set up the headers and footers on one chart (File menu > Page Setup), then before doing anything else, select each subsequent chart in turn and press the F4 key, which repeats this action. You could also select a chart, then record a macro while setting up its headers and footers. Then edit the macro code to only do the headers and footers, and leave out all that...

Charts #37
Hi All, Can you please advise if we can create one chart with multiple pivot table of the same data. Thanks Niranjan ...

Fixed straight line(s) on a chart
How can I draw a fixed straight line in an excel chart. A line on a level I can easily adjust changing the value in a spreadsheet cell, for example. Is it possible to fix even multiple lines (eg. as a fixed "bandwidth information" - data comparing to a bandwidth)? Thanks Lukas Hi See the following sites: http://andypope.info/charts/averageline.htm http://www.tushar-mehta.com/excel/charts/straight_lines/index.html http://peltiertech.com/Excel/Charts/DummySeries.html#AddLine -- Regards Frank Kabel Frankfurt, Germany "Lukas" <lukas.bieri@gmx.net> schrieb im Newsbeit...

Update Charts X-axis
I have a program with 76 charts. They all use the same range to get their x-axis (dates). At the first of every month, I change the dates to show the upcoming month. The table shows the entire year, but we just view it one month (fiscal) at a time. The series is by rows, but could be changed to columns if necessary. I inherited this program. Currently I have to update each one of them manually (click on each and change their XValues). Is there a way I can update all of them at one time? Could vba be used to select and update each chart with me just selecting the range? I am no stranger...

Access data
Is there a way to open an Access 97 database with Excel 97? Hi C, I have a hyperlink in my excel workbook that opens the access workbook, i use it for correction purposes. Would a hyperlink work? Larry -- keithl816 ------------------------------------------------------------------------ keithl816's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21287 View this thread: http://www.excelforum.com/showthread.php?threadid=492101 In Excel try Data>Import External Data>Import Data or New Database Query Gord Dibben Excel MVP On Fri, 9 Dec 2005 07:43:...

Importing data from Excel to Outlook
I have an excel spreadsheet with over 2500 contacts. The Spreadsheet is set up with Last Name, First Name in Column A, Full Address in Column B, Email Address in Column C. I have highlighted and defined a name for each of the columns. Each time I attempt to import the data into Outlook 2003 the importing and exporting window never completes its task. I tried to perform an import with a new excel workbook with only limited information and the same thing happens. Any ideas? I've had the same problem. First, save your file as a text file, then import into Outlook. HTH -- Since...

Creating a square scatter chart
I am trying to create a scatter chart, but it needs to be square (i.e., 1 X unit takes the same amount of space as 1 Y unit). Is there a way to force the chart to be square? Jon Peltier has instructions on his web site for squaring gridlines: http://www.geocities.com/jonpeltier/Excel/Charts/format.html#SqrGrid Gene wrote: > I am trying to create a scatter chart, but it needs to be > square (i.e., 1 X unit takes the same amount of space as 1 > Y unit). Is there a way to force the chart to be square? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contexture...

Web query to create historical stock data
Web query is easy to schedule 5 minute refresh, however I want to retain this 5minute data to create a database continuing throughout the trading day to generate 5 minute intraday price/volume data, my question is how can I move the data up or down so as not to remove the previous 5minutes data as the new data is generated. Any suggestions appreciated. thx You will need to (use a macro) move to another sheet. -- Don Guillett SalesAid Software dguillett1@austin.rr.com "ChrisT" <ChrisT@discussions.microsoft.com> wrote in message news:8C721E31-87C5-4C99-8119-F57557BDA5E3@m...