#N/A problem

J

Jeff

Hi,

The formula below works well. However, sometimes the result is a "#N/A" due
to incorrectly entering the data (a typo). It is a lookup formula and
causes a break in the macro. I'd like to incorporate something that if a
"#N/A" happens it is just the same as FALSE and results in "apples".

The formula is:

=IF(Data!S9="TRUE",Data!R8,"Apples")

I have read the Excel help and tried to resolve this but alas no resolution.

Any help would be greatly appreciated.

regards

Jeff
 
K

KL

Hi Jeff,

Try something like this:

=IF(ISNA(Data!S9),"Apples",IF(Data!S9="TRUE",Data!R8,"Apples"))

or

=IF(ISERROR(Data!S9),"Apples",IF(Data!S9="TRUE",Data!R8,"Apples"))

if your "TRUE" is in reality the logical value TRUE, then try one of these:

=IF(ISNA(Data!S9),"Apples",IF(Data!S9,Data!R8,"Apples"))

or

=IF(ISERROR(Data!S9),"Apples",IF(Data!S9,Data!R8,"Apples"))

Regards,
KL
 
J

Jeff

Kl,

This completely solved my problem and I appreciate your help.

I can see I got myself into a mess about "TRUE" v TRUE and have untangled it
(all the related cells now are consistently TRUE (rather than "TRUE"). I've
learned a bonus new fact.

One query - the formula I used
=IF(ISERROR(Data!S9),"Apples",IF(Data!S9,Data!R8,"Apples")) ....
on the last part it is ... IF(Data!S9,Data!R8,"Apples")). My question is
why the comma between Data!S9 and Data!R8? Does the comma mean the same as
equals?

If you don't have the time to reply, I'll understand. I'm just hungry for
understanding.

sincerely

Jeff
 
K

KL

Hi Jeff,
on the last part it is ... IF(Data!S9,Data!R8,"Apples")). My question is
why the comma between Data!S9 and Data!R8? Does the comma mean the same
as equals?
Click to expand...

Not exactly. The structure is:

IF(Condition,OutcomeIfTrue,OutcomeIfFalse)

Basically the formula

IF(Data!S9,Data!R8,"Apples")

is equal to

IF(Data!S9=TRUE,Data!R8,"Apples")

but the former is shorter.

Actually the formula:

IF(Data!S9,...)

can be used to test two things 1) if Data!S9 is equal to TRUE or 2) if
Data!S9 is not equal to 0


Regards,
KL
 
J

Jeff

KL,
Thanks again. I'll remember this one.
Jeff


KL said:
Hi Jeff,


Not exactly. The structure is:

IF(Condition,OutcomeIfTrue,OutcomeIfFalse)

Basically the formula

IF(Data!S9,Data!R8,"Apples")

is equal to

IF(Data!S9=TRUE,Data!R8,"Apples")

but the former is shorter.

Actually the formula:

IF(Data!S9,...)

can be used to test two things 1) if Data!S9 is equal to TRUE or 2) if
Data!S9 is not equal to 0


Regards,
KL
Click to expand...
 
Top