CODEWITHSUNDEEP
excelif-statement
George
George

Reputation: 347

How to reference "#VALUE!" in excel function

I am using the following formula to yield a 1 if a CS number exists in the cell, and a 0 if it does not. The nested formula works correctly.

The issue is with the outside IF function. What happens is that when it does not find a CS number, it yields #VALUE! in the cell. When that happens, I want the cell to say 1.

Using the function below, it still is filling in #VALUE! when a CS number is not found.

=IF(IF(ISERR(NUMBERVALUE(MID(K2,SEARCH("CS",K2)+M39,1))),MID(K2,SEARCH("CS",K2),9),MID(K2,SEARCH("CS",K2),10)) = "#VALUE!", 1, 0)


Is there a more appropriate way to reference this to yield 1 instead?

Upvotes: 2

Views: 648

Answers (2)

ashleedawg
ashleedawg

Reputation: 21639

Ironically, #VALUE! isn't a Value, so you can't use a comparison function like If to deal with it as if it's a string.

​

Change your formula to:

=IFERROR(IF(ISERR(NUMBERVALUE(MID(K2,SEARCH("CS",K2)+M39,1))),MID(K2,SEARCH("CS",K2),9),MID(K2,SEARCH("CS",K2),10)),1)

...and it will return a 1 instead of #VALUE! if the text you're searching for isn't found.

​

​

Upvotes: 3

Shirayuki
Shirayuki

Reputation: 189

Did you try

=IFERROR( your formula here, 1 )

Basically, it evaluates your formula as usual, but when anything in your formula returns an error, you will get an 1 in return.

Using this idea, you can wrap each part of your formula in an IFERROR to define the default value to return whenever it encounters one. Kinda wasteful though.

Upvotes: 2

Related Questions