Cant Get Nested IF/AND statements to work - have I reached limit [SOLVED]

[Cammandk]

I have a list box in A3 which depending on the selection and the date in B3 needs to return a different result in D3. I've broken out the statements but have had no success trying to combine them all together. Is there a way.

[pre]

Type	Date		Status

O	04-Jun-13	if A3 = "O" then Status = A	A
E		if A3 = "E" then Status = U	

I		if A3="I" AND B3 > than Today() then Status = A
if A3="I" AND B3 <= than Today() then Status = U	

P		if A3="P" AND B3 > than Today() then Status = A
if A3="P" AND B3 <= than Today() then Status = U	

S		If A3="S" AND B3 > than Today() then Status = A
if A3="S" AND B3 <= than Today() then Status = U	

B		if A3="B" AND DAY(B3)>20 then Status = A
if A3="B" AND DAY(B3)<20 then Status = U

[/pre]
This all takes place in Row 3 and the formula needs to be in H3

 

[Luke M]

It looks like there are only 2 possible output conditions, so a couple IF's will be sufficient. Just need to use some AND and OR functions.

=IF(OR(A3="O",AND(OR(A3="I",A3="P",A3="S"),B3>TODAY()),AND(A3="B",DAY(B3)>20)),"A",

IF(OR(A3="E",AND(OR(A3="I",A3="P",A3="S"),B3<=TODAY()),AND(A3="B",DAY(B3<=20)),"U",

"No condition met")

 

[Cammandk]

Thanks - tried this and getting parenthesis error - I've added my active cells


=IF(OR(D7="O",AND(OR(D7="I",D7="P",D7="S"),E7>TODAY()),AND(D7="B",DAY(E7)>20)),"A", IF(OR(D7="E",AND(OR(D7="I",D7="P",D7="S"),E7<=TODAY()),AND(D7="B",DAY(E7<=20)),"U","")

 

[Luke M]

Oops, sorry about that. original formula should have been:

=IF(OR(A3="O",AND(OR(A3="I",A3="P",A3="S"),B3>TODAY()),AND(A3="B",DAY(B3)>20)),"A",

IF(OR(A3="E",AND(OR(A3="I",A3="P",A3="S"),B3<=TODAY()),AND(A3="B",DAY(B3<=20))),"U",

"No condition met"))


With your active cells:

=IF(OR(D7="O",AND(OR(D7="I",D7="P",D7="S"),E7>TODAY()),AND(D7="B",DAY(E7)>20)),"A", IF(OR(D7="E",AND(OR(D7="I",D7="P",D7="S"),E7<=TODAY()),AND(D7="B",DAY(E7<=20))),"U",""))

 

[Cammandk]

Tried but couldn't get "B" to work properly - figured out missing ) on end of 2nd Day(E7 - so all good too go.

Thanks

Solved.

 

[Luke M]

Glad you got it figured out, and thanks for letting us know. =)

 

[Sajan]

Hi,

Just for fun... here is another way to support the conditions:

=LOOKUP(CHOOSE(CHOOSE(MATCH(A3, {"O","E","I","P","S","B"},0), 1,2,3,3,3,4), 1, 21, (B3<=TODAY())*21+1, DAY(B3)), {1,20,21}, {"A","Undefined","U"})


This should match the conditions in OP's original post.


Cheers,

Sajan.