#N/A problem
 

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

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",Da ta!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,"App les"))

or

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

Regards,
KL


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

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


"KL" wrote in message
...
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",Da ta!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,"App les"))

or

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

Regards,
KL


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

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?

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

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


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

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