Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
monir
 
Posts: n/a
Default #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.
  #3   Report Post  
monir
 
Posts: n/a
Default

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.


  #4   Report Post  
Roger
 
Posts: n/a
Default

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.




  #5   Report Post  
Jon Peltier
 
Posts: n/a
Default

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.






  #6   Report Post  
Roger
 
Posts: n/a
Default

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.




  #7   Report Post  
Jon Peltier
 
Posts: n/a
Default

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



  #8   Report Post  
Roger
 
Posts: n/a
Default

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" wrote in message
...
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" 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.





  #9   Report Post  
Jon Peltier
 
Posts: n/a
Default

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" wrote in message
...

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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I sum rounded values from two seperate formulas in Excel? Federali Excel Worksheet Functions 1 May 3rd 05 06:01 PM
display values of formulas in Excel Binyaaust Excel Worksheet Functions 1 April 14th 05 12:35 PM
Using SumIF formulas with multiple lookup values Chris Freeman Excel Worksheet Functions 3 April 13th 05 01:40 AM
Selecting data from a list based on entered values GrantM Excel Discussion (Misc queries) 1 December 20th 04 11:59 AM
delete values in several cells without deleting the formulas dranreb Excel Discussion (Misc queries) 4 December 9th 04 02:15 AM


All times are GMT +1. The time now is 02:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"