Hello;

When some cells of the data series have #N/A values returned by formulas,
the corresponding chart fails.
But if the #N/A values are manually entered into those same cells, the chart
works fine !!
To my understanding, Excel Charts treat cells with #N/A values as empty
cells, so one may select the relevant chart option to "leave gaps", which is
perfect.

With this apparent different interpretation (by Excel Chart) of the same
#N/A values in the data series, how can I make the #N/A values returned by
formulas acceptable by the chart ??

Thank you for your help.

The #N/A should be the result of the function NA() and not just typing
the literal "#N/A".

Also, a #N/A is *not* treated as 'leave a gap' for a line chart or a XY
Scatter chart but rather as a 'interpolate across the #N/A.'

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hello;

When some cells of the data series have #N/A values returned by formulas,
the corresponding chart fails.
But if the #N/A values are manually entered into those same cells, the chart
works fine !!
To my understanding, Excel Charts treat cells with #N/A values as empty
cells, so one may select the relevant chart option to "leave gaps", which is
perfect.

With this apparent different interpretation (by Excel Chart) of the same
#N/A values in the data series, how can I make the #N/A values returned by
formulas acceptable by the chart ??

Thank you for your help.

Tushar;

Apsolutely correct ! By having the formula returning the result of the
function NA() instead of returning the string "#N/A", the line chart problem
disappeared !

Thank you once again for your help.



"Tushar Mehta" wrote:

The #N/A should be the result of the function NA() and not just typing
the literal "#N/A".

Also, a #N/A is *not* treated as 'leave a gap' for a line chart or a XY
Scatter chart but rather as a 'interpolate across the #N/A.'

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hello;

When some cells of the data series have #N/A values returned by formulas,
the corresponding chart fails.
But if the #N/A values are manually entered into those same cells, the chart
works fine !!
To my understanding, Excel Charts treat cells with #N/A values as empty
cells, so one may select the relevant chart option to "leave gaps", which is
perfect.

With this apparent different interpretation (by Excel Chart) of the same
#N/A values in the data series, how can I make the #N/A values returned by
formulas acceptable by the chart ??

Thank you for your help.

I too wish to stop a chart line. I am using the formula
=IF(AC90=0,#N/A,AC90*2) in one column. The chart line comes from a second
column of figures =IF(AC90=#N/A,"",1). This gives me #N/A and doesn't stop
the chart line. Any suggestions would be greatly appreciated.

Roger



"monir" wrote in message
...
Tushar;

Apsolutely correct ! By having the formula returning the result of the
function NA() instead of returning the string "#N/A", the line chart
problem
disappeared !

Thank you once again for your help.



"Tushar Mehta" wrote:

The #N/A should be the result of the function NA() and not just typing
the literal "#N/A".

Also, a #N/A is *not* treated as 'leave a gap' for a line chart or a XY
Scatter chart but rather as a 'interpolate across the #N/A.'

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hello;

When some cells of the data series have #N/A values returned by
formulas,
the corresponding chart fails.
But if the #N/A values are manually entered into those same cells, the
chart
works fine !!
To my understanding, Excel Charts treat cells with #N/A values as empty
cells, so one may select the relevant chart option to "leave gaps",
which is
perfect.

With this apparent different interpretation (by Excel Chart) of the
same
#N/A values in the data series, how can I make the #N/A values returned
by
formulas acceptable by the chart ??

Thank you for your help.

Hi Roger -

To return #N/A in a formula, use NA() in the formula:

=IF(AC90=0,NA(),AC90*2)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Roger wrote:

I too wish to stop a chart line. I am using the formula
=IF(AC90=0,#N/A,AC90*2) in one column. The chart line comes from a second
column of figures =IF(AC90=#N/A,"",1). This gives me #N/A and doesn't stop
the chart line. Any suggestions would be greatly appreciated.

Roger



"monir" wrote in message
...

Tushar;

Apsolutely correct ! By having the formula returning the result of the
function NA() instead of returning the string "#N/A", the line chart
problem
disappeared !

Thank you once again for your help.



"Tushar Mehta" wrote:


The #N/A should be the result of the function NA() and not just typing
the literal "#N/A".

Also, a #N/A is *not* treated as 'leave a gap' for a line chart or a XY
Scatter chart but rather as a 'interpolate across the #N/A.'

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...

Hello;

When some cells of the data series have #N/A values returned by
formulas,
the corresponding chart fails.
But if the #N/A values are manually entered into those same cells, the
chart
works fine !!
To my understanding, Excel Charts treat cells with #N/A values as empty
cells, so one may select the relevant chart option to "leave gaps",
which is
perfect.

With this apparent different interpretation (by Excel Chart) of the
same
#N/A values in the data series, how can I make the #N/A values returned
by
formulas acceptable by the chart ??

Thank you for your help.

Hi Jon, good to hear from you and thanks for your reply. I actually looked
at your website prior to sending the previous request. I appear to have
sent the wrong information for my request so here are the actual formulae
and details. This first formula gives different results depending on
whether there is a number or a blank in H column
=IF(H86=0,#N/A,H86*SUM(J$7:J86)/J$7). I would prefer not to adjust this if
it is possible as it affects several other columns as well.



The problem column formula is

=IF(ISERROR(BN86),"",IF(ROW() Selection!$D$8,"",(BN86-BN$7)/BN$7))

This is the column where I want the chart line stopped if there is no
numerical value from the previous formula.



Roger



"Jon Peltier" wrote in message
...
Hi Roger -

To return #N/A in a formula, use NA() in the formula:

=IF(AC90=0,NA(),AC90*2)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Roger wrote:

I too wish to stop a chart line. I am using the formula
=IF(AC90=0,#N/A,AC90*2) in one column. The chart line comes from a
second column of figures =IF(AC90=#N/A,"",1). This gives me #N/A and
doesn't stop the chart line. Any suggestions would be greatly
appreciated.

Roger



"monir" wrote in message
...

Tushar;

Apsolutely correct ! By having the formula returning the result of the
function NA() instead of returning the string "#N/A", the line chart
problem
disappeared !

Thank you once again for your help.



"Tushar Mehta" wrote:


The #N/A should be the result of the function NA() and not just typing
the literal "#N/A".

Also, a #N/A is *not* treated as 'leave a gap' for a line chart or a XY
Scatter chart but rather as a 'interpolate across the #N/A.'

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...

Hello;

When some cells of the data series have #N/A values returned by
formulas,
the corresponding chart fails.
But if the #N/A values are manually entered into those same cells, the
chart
works fine !!
To my understanding, Excel Charts treat cells with #N/A values as empty
cells, so one may select the relevant chart option to "leave gaps",
which is
perfect.

With this apparent different interpretation (by Excel Chart) of the
same
#N/A values in the data series, how can I make the #N/A values returned
by
formulas acceptable by the chart ??

Thank you for your help.