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...
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?
3 Answers
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.
Maya is a hardware enthusiast who tests and reviews smart home devices, smartphones, wearables, and audio gear. She focuses on practical consumer value and build quality.
