78

I'd like to write a formula such that if cell A1 displays #VALUE!, say TRUE in cell B1.

Here's my formula in cell B1:

=IF(A1="#VALUE!", "TRUE", "FALSE")

I get FALSE when A1 does not say #VALUE! so that part is fine. But, when it does say #VALUE!, I get a #VALUE! error in cell B1, when I want it to say TRUE. How do I do this?

Excellll
  • 5,609
  • 4
  • 38
  • 55
MrPatterns
  • 4,184
  • 27
  • 65
  • 85
  • 1
    Forgot to mention, the #VALUE error in cell A1 is caused by another formula...and I'm not trying to look for a string "#VALUE". – MrPatterns May 21 '12 at 16:48

3 Answers3

126

Use IFERROR(value, value_if_error)

Charleh
  • 13,749
  • 3
  • 37
  • 57
46

This will return TRUE for #VALUE! errors (ERROR.TYPE = 3) and FALSE for anything else.

=IF(ISERROR(A1),ERROR.TYPE(A1)=3)
Excellll
  • 5,609
  • 4
  • 38
  • 55
  • 9
    +1. Worth pointing out that this one will work in versions before XL 2007 and, since it targets only #VALUE!, will avoid masking other errors. Interesting discussion of IFERROR here: http://www.dailydoseofexcel.com/archives/2012/04/18/is-iferror-bad/ – Doug Glancy May 21 '12 at 20:04
0

in EXCEL 2013 i had to use IF function 2 times: 1st to identify error with ISERROR and 2nd to identify the specific type of error by ERROR.TYPE=3 in order to address this type of error. This way you can differentiate between error you want and other types.

Mirec Tkáč
  • 1
  • 2