Best IT Recipes

How to know "#VALUE!" in Excel spreadsheets?

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

Here is my formula in cell B1:

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

I get FALSE when A1 does not say #VALUE! so the part is ok. But when he says #VALUE! I get the error #VALUE! in cell B1 when I want to say TRUE . How to do it?

+44
excel excel-formula
May 21 '12 at 16:47
source share
3 answers

Use IFERROR(value, value_if_error)

+79
May 21 '12 at 16:49
source share

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

 =IF(ISERROR(A1),ERROR.TYPE(A1)=3)
+37
May 21 '12 at 17:15
source share

in EXCEL 2013, I had to use the IF function 2 times: 1st to determine the error with ISERROR and 2nd to determine the specific type of error using ERROR.TYPE = 3 to eliminate this type of error. Thus, you can distinguish between the required error and other types.

0
Sep 16 '14 at 10:25
source share


More articles:


All Articles