Handling #VALUE?

  • Thread starter Thread starter (PeteCresswell)
  • Start date Start date
P

(PeteCresswell)

Sample Code:
------------------------------------------
=IF(SEARCH("Taxable",RC[3])=1, TRUE,FALSE)
------------------------------------------

If "Taxable" is found, no prob: result= "True".

OTOH, if "Taxable" not found, SEARCH (just like
the documentation says....) returns #VALUE.


Question: How do I trap for #VALUE in the IF() statement
and convert it to "FALSE"
 
Question: How do I trap for #VALUE

I'm thinking it's something with ERROR.TYPE, but I can't make it work.

e.g.
=IF(ERROR.TYPE((SEARCH("Taxable",RC[3]))=3),FALSE,IF(SEARCH("Taxable",RC[3])=1,
TRUE,FALSE))

Returns "FALSE" when it should, but returns #NA instead of TRUE when
the string is found.
 
The ERROR.TYPE function will fail if it is passed anything that is not an
error. Therefore, you must use ISERROR to first test whether a cell has an
error and then, if so, call ERROR.TYPE to find the type of error. E.g.,

=IF(ISERROR(A1),ERROR.TYPE(A1),"No Error")


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




PeteCresswell said:
Question: How do I trap for #VALUE
Click to expand...

I'm thinking it's something with ERROR.TYPE, but I can't make it work.

e.g.
=IF(ERROR.TYPE((SEARCH("Taxable",RC[3]))=3),FALSE,IF(SEARCH("Taxable",RC[3])=1,
TRUE,FALSE))

Returns "FALSE" when it should, but returns #NA instead of TRUE when
the string is found.
Click to expand...
 
The ERROR.TYPE function will fail if it is passed anything that is not an
error. Therefore, you must use ISERROR to first test whether a cell has an
error and then, if so, call ERROR.TYPE to find the type of error. E.g.,

=IF(ISERROR(A1),ERROR.TYPE(A1),"No Error")
Click to expand...


That did it:

=IF(ISERROR(IF(SEARCH("Taxable",RC[3])=1,
TRUE,FALSE)),FALSE,IF(SEARCH("Taxable",RC[3])=1, TRUE,FALSE))

Thanks!
 
Back
Top