#REF or #N/A with "IF" formula

[F. Lawrence Kulchar]

FLKulchar
In cell A1, I have either #N/A or #REF because, there 'was' an error in my
formula!

How come...=IF(A1="N/A", 3,4) does NOT work?

How can I make it to work...in other words, if cell A1 has a #N/A in it, how
can I make the 'IF' formula work???

Same is true for the "#REF" error!!

Please help.
FLKulchar

 

[John C]

Look in HELP under ERROR.TYPE function.
If an error occurs
=ERROR.TYPE(A1) for #N/A = 7, and for #REF = 4

 

[Mike H]

Try

=IF(ISNA(A1), 3,4)

Mike

 

[T. Valko]

It depends on what exactly you want.

If you want to test A1 for *any error*:

=IF(ISERROR(A1),3,4)

If you want to test for #N/A:

=IF(ISNA(A1),3,4)

If you want to test for #REF!:

=IF(ISNA((ERROR.TYPE(A1)=4)),4,3)

 

[T. Valko]

Ooops!

Yeah, you're right.

Disregard that formula.

 

[ShaneDevenshire]

Hi,

you asked "how come...."

The reason is because "N/A" which you enterd is a text entry and what Excel
is returning when you see #N/A is an error value, not text. This is true for
all 7 errors that Excel displays such as NAME?, VALUE# and #REF!

You can trap these types of errors, depending on which one, using
ISERR
ISNA
ISERROR
and you can determine which one it is by using ERROR.TYPE
You should look each of these up in the Help system to understand what the
differences are and what the return value is from the last one.