Urgent - How do I ignore "n.a." values in cells when building an IF+AND formula?

Copper Contributor



I want my IF+AND formula to ignore cells with the value "n.a.". I have a big data set in Excel where some cells contain the value n.a. hardcoded. I want to ignore these n.a. values in my formulas.



A1=4, B1=n.a. and C1=n.a. 

I want to use a formula to check if ALL cells A1, B1 and C1 together contains values larger than 3.

I use the formula +IF(AND(A1>=3,B1>=3,C1>=3),"Pass","No Pass").

In this example we all know that only cell A1 contains a value larger than 3. HOWEVER, my formula gives the result "Pass". I therefore need advice on how to change the formula so it treats these hardcoded "n.a." values as zero OR ignores them. I have tried figuring out IFNA but to no luck. 

Big appreciation in advance for the help! :smile:


2 Replies
best response confirmed by Excelo0 (Copper Contributor)



=IF(AND(A1:C1>=3, ISNUMBER(A1:C1)),"Pass","No Pass")

Thank you, much appreciated. Golden star to you Sir! Please go into the weekend feeling like the hero you are!