Complicated Nested IF with OR & AND

IF (
(CF2 = LONDON OR CF2 = PARIS OR CF2 = WASHINGTON DC) AND (CG2 = Wednesday) AND (CH2 = 19 OR CH2 = 20 OR CH2 = 21 OR CH2 = 22 OR CH2 = 23 OR CH2 = 0 OR CH2 = 1 OR CH2 = 2 OR CH2 = 2)
OR
(CF2 = ATHENS OR CF2 = ATLANTA OR CF2 = ROME) AND (CG2 = Thursday) AND (CH2 = 19 OR CH2 = 20 OR CH2 = 21 OR CH2 = 22 OR CH2 = 23 OR CH2 = 0 OR CH2 = 1 OR CH2 = 2 OR CH2 = 2)
) THEN Y ELSE N

So basically it's the first condition OR the second condition then return Y else N

=IF(OR(AND(OR(CF2={"LONDON","PARIS","WASHINGTON DC"}),CG2="Wednesday",OR(CH2={0,1,2,19,20,21,22,23})),AND(OR(CF2={"ATHENS","ATLANTA","ROME"}),CG2="Thursday",OR(CH2={0,1,2,19,20,21,22,23}))),"Y","N")

I think. You could also simplify the CH2 part as it is the same for both.

Hi

maybe

Please Login or Register  to view this content.

To be doublechecked

Regards

Hi

Something like this, maybe??


=IF(OR(AND(OR(CF2="LONDON",CF2="PARIS",CF2="WASHINGTON" ),CG2="Wednesday",OR(CH2=19,CH2=20,CH2=21,CH2=22,CH2=23,CH2=0,CH2=1,CH2=2,CH2= 2)),AND(O(CF2="ATHENS",CF2="ATLANTA",CF2= "ROME" ),CG2="Thursday",OR(CH2=19,CH2=20,CH2=21,CH2=22,CH2=23,CH2=0,CH2=1,CH2=2,CH2=2))),"Y","N")

Hope to helps you.


Note: CANAPONE: Same formula. Sorry!

Simplified version of my previous

=IF(AND(OR(CH2={0,1,2,19,20,21,22,23}),OR(AND(OR(CF2={"LONDON","PARIS","WASHINGTON DC"}),CG2="Wednesday"),AND(OR(CF2={"ATHENS","ATLANTA","ROME"}),CG2="Thursday"))),"Y","N")

So complicated with AND(OR(AND...
Also try to avoid sth repeat twice or more...
Try to figure it out in simplest way:
IF numberlist, then IF Wednesday, then IF Thusday, else

=IF(CH2<>{0,1,2,19,20,21,22,23},"N",IF(CG2="Wednesday",IF(OR(CF2={"LONDON","PARIS","WASHINGTON DC"}),"Y","N"),IF(CG2="Thursday",IF(OR(CF2={"ATHENS","ATLANTA","ROME"}),"Y","N"),"N")))

@bebo021999
Ignoring for a moment the fact that your formula doesn't work as posted, how is it really simpler than what I suggested?

@OEG0,
Would you mind to show me the incorrect case?
Nice to learn from you.

Try using London, Wednesday and 1 (or indeed any valid number other than 0) and it will return N, not Y.

Oh Oh, it's my faul. Forgot that =IF(CH2<>{0,1,2,19,20,21,22,23},... always returns 0 if false
may replaced by: = IF(ISERROR(MATCH(CH2,{0,1,2,19,20,21,22,23},0)),...
By this, a litle bit longer.

Tks a lot,