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

[monir]

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

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

 

[monir]

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.

 

[Roger]

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

 

[Jon Peltier]

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]

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]

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]

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]

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