CODEWITHSUNDEEP
excel
ManishChristian
ManishChristian

Reputation: 3784

EXCEL formula with multiple conditions

I am in turmoil of IF, OR and AND in Excel formula.

Basically, I have two different cells. I want to first check that each cell is have Text or Number and if its having either of it, then I want to compare both cells and display the result in Third cell.

So it would be like

IF  
Cell A2 --> Check if it contains Text or Number  
AND  
Cell B2 --> Check if it contains Text or Number  
THEN  
Cell C2 --> Compare A2 and B2

Example 1: Positive 

         A              B              C    
1        abc123         abc123         PASS

Example 2: Negative 

         A              B              C    
1        abc123         abc113         FAIL

Example 3: Negative 

         A              B              C    
1        113            113            FAIL

I hope above example will help.

So far, I've tried: 

=IF((IF(OR(ISNUMBER(A2),ISTEXT(A2)),A2,""))=(IF(OR(ISNUMBER(B2),ISTEXT(B2)),B2,"")),"PASS","FAIL")

In above example, if I leave any cell empty, still it is showing Pass.

Thanks in advance...

Upvotes: 1

Views: 4191

Answers (2)

Andy G
Andy G

Reputation: 19367

I would use:

=IF(AND(ISNUMBER(A1),ISNUMBER(B1),A1=B1),"Pass",IF(AND(A1<>"",B1<>"",A1=B1),"Pass","Fail"))

Upvotes: 1

Tro
Tro

Reputation: 909

=IF(AND(OR(ISNUMBER(A2), ISTEXT(A2)), OR(ISNUMBER(B2), ISTEXT(B2))), IF(A2 = B2, "PASS", "FAIL"), "")

Upvotes: 4

Related Questions