• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF, OR, AND Formula

R

rod411

New Member
I am having difficulty inputting the correct formula for the following project I need.
These are my conditions:

A2="Yes", B2="Yes", C2="Yes” this should return a YES
A2="Yes", B2="Yes", C2="No" this should return a YES
A2="Yes", B2="No", C2="Yes" this should return a YES
A2="Yes", B2="No", C2="No" this should return a No
A2="No", B2="No", C2="No" this should return a No
A2="No", B2="Yes", C2="Yes" this should return a No
A2="No", B2="No", C2="Yes" this should return a No
A2="No", B2="Yes", C2="No" this should return a No

This is what I have but it only gives me YES to all.

=IF(OR(AND(A2="Yes",B2="Yes",C2="Yes"),AND(A2="Yes",B2="Yes",C2="No"),AND(A2="Yes",B2="No",C2="Yes"),AND(A2="Yes",B2="No",C2="No"),AND(A2="No",B2="No",C2="No"),AND(A2="No",B2="Yes",C2="Yes"),AND(A2="No",B2="No",C2="Yes"),AND(A2="No",B2="Yes",C2="No")),"Yes","No")
 
rod411 said:
I am having difficulty inputting the correct formula for the following project I need.
These are my conditions:

A2="Yes", B2="Yes", C2="Yes” this should return a YES
A2="Yes", B2="Yes", C2="No" this should return a YES
A2="Yes", B2="No", C2="Yes" this should return a YES
A2="Yes", B2="No", C2="No" this should return a No
A2="No", B2="No", C2="No" this should return a No
A2="No", B2="Yes", C2="Yes" this should return a No
A2="No", B2="No", C2="Yes" this should return a No
A2="No", B2="Yes", C2="No" this should return a No

This is what I have but it only gives me YES to all.

=IF(OR(AND(A2="Yes",B2="Yes",C2="Yes"),AND(A2="Yes",B2="Yes",C2="No"),AND(A2="Yes",B2="No",C2="Yes"),AND(A2="Yes",B2="No",C2="No"),AND(A2="No",B2="No",C2="No"),AND(A2="No",B2="Yes",C2="Yes"),AND(A2="No",B2="No",C2="Yes"),AND(A2="No",B2="Yes",C2="No")),"Yes","No")
Click to expand...
Hi

Try this

=IF(AND(A2="Yes",OR(B2="yes",C2="yes")),"Yes",IF(A2="No","No",IF(AND(B2="No",C2="No"),"No","")))
 
Hi ,

Rather than concentrate on Excel formulae , look on it as a problem in logic ; by looking at each rule in isolation , you are missing the forest for the trees. If you can look for a pattern in all of the rules , it goes like this :

If A2 = "No" , the output is "No" - the status of B2 or C2 is irrelevant.

If A2 = "Yes" , the output can be looked at in 2 ways viz. if either B2 = "Yes" or C2 = "Yes" , the output is "Yes" ; you can also see it as if both B2 and C2 are "No" , then the output is "No" , else it is "Yes".

Looked at in this manner , you need only 2 IF checks if you want to keep it simple ; otherwise you can combine it the way Hui has posted.

Keeping it simple , you have :

=IF(A2 = "No", "No" , IF(AND(B2 = "No" , C2 = "No") , "No" , "Yes"))

=IF(A2 = "No", "No" , IF(OR(B2 = "Yes" , C2 = "Yes") , "Yes" , "No"))

Narayan
 
Syedali
My formula
=IF(or(A2="No",and(B2="No",C2="No")),"No","Yes")

is 20 characters shorter/simpler than yours
=IF(B9="no","no",IF(AND(B9="yes",OR(C9="yes",D9="yes")),"yes","No"))

It's logic is described by Narayan above
 
Hui said:
Syedali
My formula
=IF(or(A2="No",and(B2="No",C2="No")),"No","Yes")

is 20 characters shorter/simpler than yours
=IF(B9="no","no",IF(AND(B9="yes",OR(C9="yes",D9="yes")),"yes","No"))

It's logic is described by Narayan above
Click to expand...

No Sir, You are a Genius i know.

I told to who make this question is so complicated. Narayan quoted the question easily.
Sorry if my answer irritate you.. sorry once again
 
If the O/P was happy with True for Yes and False for No he could simplify it to:
=NOT(OR(A2="No",AND(B2="No",C2="No")))
Saving another 10 characters
 
You must log in or register to reply here.
Back
Top