How do I recognize "#VALUE!" in Excel spreadsheets?

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?

excel
excel-formula

189585 次浏览

小开

最佳答案

Use IFERROR(value, value_if_error)

小开

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

=IF(ISERROR(A1),ERROR.TYPE(A1)=3)

小开

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.