Thread: #N/A Values : Returned by Formulas vs Entered Manually

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 <FB2D921C-14A0-42B2-84DD-EBAF458A2EDE@microsoft.com>,
monir@discussions.microsoft.com 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 <FB2D921C-14A0-42B2-84DD-EBAF458A2EDE@microsoft.com>,
> monir@discussions.microsoft.com 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" <monir@discussions.microsoft.com> wrote in message
news:8252DE7F-65B3-4D3E-8074-3045D15899CD@microsoft.com...
> 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 <FB2D921C-14A0-42B2-84DD-EBAF458A2EDE@microsoft.com>,
>> monir@discussions.microsoft.com 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" <monir@discussions.microsoft.com> wrote in message
> news:8252DE7F-65B3-4D3E-8074-3045D15899CD@microsoft.com...
>
>>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 <FB2D921C-14A0-42B2-84DD-EBAF458A2EDE@microsoft.com>,
>>>monir@discussions.microsoft.com 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" <jonREMOVExlmvp@peltierCAPStech.com> wrote in message
news:%23a88hsDgFHA.1284@TK2MSFTNGP14.phx.gbl...
> 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" <monir@discussions.microsoft.com> wrote in message
>> news:8252DE7F-65B3-4D3E-8074-3045D15899CD@microsoft.com...
>>
>>>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 <FB2D921C-14A0-42B2-84DD-EBAF458A2EDE@microsoft.com>,
>>>>monir@discussions.microsoft.com 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.
>>>>>
>>>>
>>

A zero or blank in H86 gives the same answer in this formula:

=IF(H86=0,#N/A,H86*SUM(J$7:J86)/J$7)

but if the blank is not a blank after all but another formula returning
"", you can use this:

=IF(OR(H86=0,LEN(H86)=0),NA(),H86*SUM(J$7:J86)/J$7)

Note the use of NA() instead of #N/A. It probably doesn't matter in this
case, but it's sharper to use NA().

In the second formula, I'd replace both "" with NA().

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

Roger wrote:
> 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" <jonREMOVExlmvp@peltierCAPStech.com> wrote in message
> news:%23a88hsDgFHA.1284@TK2MSFTNGP14.phx.gbl...
>
>>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" <monir@discussions.microsoft.com> wrote in message
>>>news:8252DE7F-65B3-4D3E-8074-3045D15899CD@microsoft.com...
>>>
>>>
>>>>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 <FB2D921C-14A0-42B2-84DD-EBAF458A2EDE@microsoft.com>,
>>>>>monir@discussions.microsoft.com 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, thanks so much for your solution to my second formula. It stops the
chart lines exactly as requested. There is one problem it creates which is
not too important and that is a calculation to determine the highest and
lowest number in the above column which has many scenarios using essentially
the same formula except for the column designation. I could solve this by
puting my old formula into a second column for each section but this would
make the file much larger. If you have a better way of solving this it
would be much appreciated.

Thanks again,

Roger



"Jon Peltier" <jonREMOVExlmvp@peltierCAPStech.com> wrote in message
news:usqXnaYgFHA.3316@TK2MSFTNGP14.phx.gbl...
>A zero or blank in H86 gives the same answer in this formula:
>
> =IF(H86=0,#N/A,H86*SUM(J$7:J86)/J$7)
>
> but if the blank is not a blank after all but another formula returning
> "", you can use this:
>
> =IF(OR(H86=0,LEN(H86)=0),NA(),H86*SUM(J$7:J86)/J$7)
>
> Note the use of NA() instead of #N/A. It probably doesn't matter in this
> case, but it's sharper to use NA().
>
> In the second formula, I'd replace both "" with NA().
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> Roger wrote:
>> 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" <jonREMOVExlmvp@peltierCAPStech.com> wrote in message
>> news:%23a88hsDgFHA.1284@TK2MSFTNGP14.phx.gbl...
>>
>>>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" <monir@discussions.microsoft.com> wrote in message
>>>>news:8252DE7F-65B3-4D3E-8074-3045D15899CD@microsoft.com...
>>>>
>>>>
>>>>>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 <FB2D921C-14A0-42B2-84DD-EBAF458A2EDE@microsoft.com>,
>>>>>>monir@discussions.microsoft.com 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.
>>>>>>>
>>>>>>
>>

Roger -

You mean the formula craps out because of the error? Try this.

If I have 20 values, including some errors, this fails:

=MAX(A1:A20)

But I can write a formula that effectively ignores the errors:

{=MAX(IF(NOT(ISERROR(A1:A20)),A1:A20,-1E+300))}

This is an array formula, so don't type the {curly braces}. Hold down
CTRL-SHIFT while pressing Enter, and if it's done properly, Excel drawns
them for you. Basically the formula takes the maximum of either the
value, if there's no error, or -1E300, which is a very negative number.
Your MAX is not likely to be anywhere near this number.

A matching array formula for minimum:

{=MIN(IF(NOT(ISERROR(A1:A20)),A1:A20,1E+300))}

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


Roger wrote:

> Hi Jon, thanks so much for your solution to my second formula. It stops the
> chart lines exactly as requested. There is one problem it creates which is
> not too important and that is a calculation to determine the highest and
> lowest number in the above column which has many scenarios using essentially
> the same formula except for the column designation. I could solve this by
> puting my old formula into a second column for each section but this would
> make the file much larger. If you have a better way of solving this it
> would be much appreciated.
>
> Thanks again,
>
> Roger
>
>
>
> "Jon Peltier" <jonREMOVExlmvp@peltierCAPStech.com> wrote in message
> news:usqXnaYgFHA.3316@TK2MSFTNGP14.phx.gbl...
>
>>A zero or blank in H86 gives the same answer in this formula:
>>
>> =IF(H86=0,#N/A,H86*SUM(J$7:J86)/J$7)
>>
>>but if the blank is not a blank after all but another formula returning
>>"", you can use this:
>>
>> =IF(OR(H86=0,LEN(H86)=0),NA(),H86*SUM(J$7:J86)/J$7)
>>
>>Note the use of NA() instead of #N/A. It probably doesn't matter in this
>>case, but it's sharper to use NA().
>>
>>In the second formula, I'd replace both "" with NA().
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>Roger wrote:
>>
>>>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" <jonREMOVExlmvp@peltierCAPStech.com> wrote in message
>>>news:%23a88hsDgFHA.1284@TK2MSFTNGP14.phx.gbl...
>>>
>>>
>>>>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" <monir@discussions.microsoft.com> wrote in message
>>>>>news:8252DE7F-65B3-4D3E-8074-3045D15899CD@microsoft.com...
>>>>>
>>>>>
>>>>>
>>>>>>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 <FB2D921C-14A0-42B2-84DD-EBAF458A2EDE@microsoft.com>,
>>>>>>>monir@discussions.microsoft.com 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.
>>>>>>>>
>>>>>>>
>
>