If with Or & And

[Kathryn2]

I have multiple conditions for Or and And that need to be in an If statement. How do I write the IF statement?

Thanks in advance!

Conditions:[TABLE="width: 265"]
<tbody>[TR]
[TD]If A or B is no, then no
[/TD]
[/TR]
[TR]
[TD]If A or B is maybe, then maybe
[/TD]
[/TR]
[TR]
[TD]If A or B is NA, then take the other cell
[/TD]
[/TR]
[TR]
[TD]
Else, Yes

[TABLE="width: 331"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]Should answer
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD]Maybe
[/TD]
[TD]Yes
[/TD]
[TD]Maybe
[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD]No
[/TD]
[TD]Maybe
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD]NA
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[LxQ]

how about this:

=IF(OR(A="No",B="No"),"No",IF(OR(A="Maybe",B="Maybe"),"Maybe",IF(A="NA",B,IF(B="NA",A,"Yes"))))

so what happens if both are NA?

Edit: actually, I just noticed that there are conflicting things...

What happens if one is "No" and the other is "Maybe?" You say if either is a No, then No, and if either is a Maybe then Maybe...

 

[Kathryn2]

how about this:

=IF(OR(A="No",B="No"),"No",IF(OR(A="Maybe",B="Maybe"),"Maybe",IF(A="NA",B,IF(B="NA",A,"Yes"))))

so what happens if both are NA?

Edit: actually, I just noticed that there are conflicting things...

What happens if one is "No" and the other is "Maybe?" You say if either is a No, then No, and if either is a Maybe then Maybe...

----
Sorry,

If one is "No" and the other is maybe, then it's "No" (No overrides them all)
Both will never be NA

Thanks!

 

[JASONRUBALCAVA]

i came up with this.. but there is conflict with the "maybe' and the NO..

Sheet2

	A	B	C	D	E
A	B	Should answer
Yes	Yes	Yes		yes
Maybe	Yes	Maybe		maybe
No	Maybe	No		maybe
NA	Yes	Yes		Yes

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula E2 =IF(A2="","",IF(A2=B2,"yes",IF(OR(A2="maybe",B2="maybe"),"maybe",IF(OR(A2="no",B2="no"),"no",IF(A2="na",B2,IF(B2="na",A2,"")))))) E3 =IF(A3="","",IF(A3=B3,"yes",IF(OR(A3="maybe",B3="maybe"),"maybe",IF(OR(A3="no",B3="no"),"no",IF(A3="na",B3,IF(B3="na",A3,"")))))) E4 =IF(A4="","",IF(A4=B4,"yes",IF(OR(A4="maybe",B4="maybe"),"maybe",IF(OR(A4="no",B4="no"),"no",IF(A4="na",B4,IF(B4="na",A4,"")))))) E5 =IF(A5="","",IF(A5=B5,"yes",IF(OR(A5="maybe",B5="maybe"),"maybe",IF(OR(A5="no",B5="no"),"no",IF(A5="na",B5,IF(B5="na",A5,"")))))) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[sunilboreddy]

how about this:

=IF(OR(A="No",B="No"),"No",IF(OR(A="Maybe",B="Maybe"),"Maybe",IF(A="NA",B,IF(B="NA",A,"Yes"))))

so what happens if both are NA?

Edit: actually, I just noticed that there are conflicting things...

What happens if one is "No" and the other is "Maybe?" You say if either is a No, then No, and if either is a Maybe then Maybe...

I hope this solves your problem

=IF(OR(A1="no",B1="no"),"no",IF(OR(A1="maybe",B1="maybe"),"maybe",IF(OR(A1="yes",B1="yes"),"yes","")))

 

[LxQ]

IF(A2=B2,"yes",

if A2 and B2 are both "no", wouldn't this formula give you a yes?

 

[sunilboreddy]

Hi LXQ,

I feel that my solution would be appropriate, since if works in the priority basis. So i have placed no as highest priority, then may be, then yes.
Correct me if i am wrong.

Thanks!

 

[Kathryn2]

It works! Thank you!!

 

[Kathryn2]

PS - I had to change the Yes part of the formula to the same format as the others because the "IF(A2=B2,"yes"," was returning yes for ones that had "maybe" and "maybe". thanks again!

 

[LxQ]

Hi LXQ,

I feel that my solution would be appropriate, since if works in the priority basis. So i have placed no as highest priority, then may be, then yes.
Correct me if i am wrong.

Thanks!

I guess it's whatever Kathryn wants... I tested your formula and it works, the only difference from mine is how it handles empty cells.. Yours has no coding for "NA" and mine has no coding for empty cells, so it depends on where NA comes from and whether empty cells can be part of this table.. For the "yes," "no," and "maybe" entries, which is what I think Kathryn is interested in, both of our formulas do the job...