Forum Discussion

Excelo0's avatar
Excelo0
Copper Contributor
Sep 01, 2023
Solved

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

Hi, 

 

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.

 

Example:

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:

 

Resources