lundi 20 novembre 2017

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. Is there a more appropriate way to reference this to yield 1 instead?

'=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)

Aucun commentaire:

Enregistrer un commentaire