Forum Discussion
Excelo0
Sep 01, 2023Copper Contributor
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!
2 Replies
Sort By
- Excelo0Copper ContributorThank you, much appreciated. Golden star to you Sir! Please go into the weekend feeling like the hero you are!