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

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

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

Look in HELP under ERROR.TYPE function.
If an error occurs
=ERROR.TYPE(A1) for #N/A = 7, and for #REF = 4
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"F. Lawrence Kulchar" wrote:

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

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

Try

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

Mike

"F. Lawrence Kulchar" wrote:

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

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

That would be OK if you were looking for the text string "N/A", but if you
are looking for the error condition #N/A then the formula you need is
=IF(ISNA(A1), 3,4)
If you wanted to test for the #REF error, then you could use
=IF(ISERR(A1),IF(ERROR.TYPE(A1)=4, 3,4),4)
--
David Biddulph

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

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

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)

--
Biff
Microsoft Excel MVP


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

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

=IF(ISNA((ERROR.TYPE(A1)=4)),4,3) will of course return 3 for #N/A (or for
other errors such as !DIV/0!), as well as for #REF!.
--
David Biddulph

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

--
Biff
Microsoft Excel MVP


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

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

Ooops!

Yeah, you're right.

Disregard that formula.

--
Biff
Microsoft Excel MVP


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
=IF(ISNA((ERROR.TYPE(A1)=4)),4,3) will of course return 3 for #N/A (or for
other errors such as !DIV/0!), as well as for #REF!.
--
David Biddulph

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

--
Biff
Microsoft Excel MVP


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

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

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.


--
Thanks,
Shane Devenshire


"F. Lawrence Kulchar" wrote:

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