Multiple AND and IF

Hello, my first post here :D


I am trying to make a formula to test for certain cases, it looks like this, but doesnt work... Any help appreciated! For easy of reading I have put every case on a new line, of course this is not so in Excel.

There are 16 cases: Cells C27 to C30 can be 1 or 2 and should give the a,b,c,d or e as result in C32.

1 1111 a
2 1112 a
3 1121 b
4 1122 a
5 1211 b
6 1212 a
7 1221 c
8 1222 b
9 2111 c
10 2112 b
11 2121 d
12 2122 c
13 2211 d
14 2212 c
15 2221 e
16 2222 d


=IF(AND(C27 = 1; C28 = 1; C29 = 1; C30 = 1);"PL = a";
=IF(AND(C27 = 1; C28 = 1; C29 = 1; C30 = 2);"PL = a";
=IF(AND(C27 = 1; C28 = 1; C29 = 2; C30 = 1);"PL = b";
=IF(AND(C27 = 1; C28 = 1; C29 = 2; C30 = 2);"PL = a";
=IF(AND(C27 = 1; C28 = 2; C29 = 1; C30 = 1);"PL = b";
=IF(AND(C27 = 1; C28 = 2; C29 = 1; C30 = 2);"PL = a";
=IF(AND(C27 = 1; C28 = 2; C29 = 2; C30 = 1);"PL = c";
=IF(AND(C27 = 1; C28 = 2; C29 = 2; C30 = 2);"PL = b";
=IF(AND(C27 = 2; C28 = 1; C29 = 1; C30 = 1);"PL = c";
=IF(AND(C27 = 2; C28 = 1; C29 = 1; C30 = 2);"PL = b";
=IF(AND(C27 = 2; C28 = 1; C29 = 2; C30 = 1);"PL = d";
=IF(AND(C27 = 2; C28 = 1; C29 = 2; C30 = 2);"PL = c";
=IF(AND(C27 = 2; C28 = 2; C29 = 1; C30 = 1);"PL = d";
=IF(AND(C27 = 2; C28 = 2; C29 = 1; C30 = 2);"PL = c";
=IF(AND(C27 = 2; C28 = 2; C29 = 2; C30 = 1);"PL = e";
=IF(AND(C27 = 2; C28 = 2; C29 = 2; C30 = 2);"PL = d";"ERROR")


EDIT: of course I tried to end the formula like this "....C30 = 2);"PL = d";"ERROR"))))))))))))))))" Did not work...

you don't have any results for the IF formulas.
this...
=IF(AND(C27 = 1; C28 = 1; C29 = 1; C30 = 1);"PL = a";=IF(AND(C27 = 1; C28 = 1; C29 = 1; C30 = 2);"PL = a";
You don't need the additional "=" in front of each IF, you also don't state what should happen for each IF, you have IF(AND(this and this and this)with this ... now do this, then if not go to the second IF. You don't have that.
follow the directions in the yellow banner at the top if you want additional help.

and what is PL=a etc? Is PL a cell, where is PL=a coming from?

this could be a shorter way to write it but still you'd have to define what to do with each item...
=if(and(or(sum(c27:c30)=4,sum(c27:c30)=5,sum(c27:c30)=6),"PL = a"),"do this",if(and(or(sum(c27:c30)=4,sum(c27:c30)=5,sum(c27:c30)=6),"PL = b"), etc. until the end.
and replacing the commas with semi-colons per your regional settings.
Of course this is untested.

Thanks for your help!

I just solved it, I did not need the = every time and I needed 16 ) to close the 16 that were opened before... Logic is beautiful!

EDIT: this is the formula now: (ALS = IF, EN = AND)


=ALS(EN(C27 = 1; C28 = 1; C29 = 1; C30 = 1);"PL = a";ALS(EN(C27 = 1; C28 = 1; C29 = 1; C30 = 2);"PL = a";ALS(EN(C27 = 1; C28 = 1; C29 = 2; C30 = 1);"PL = b";ALS(EN(C27 = 1; C28 = 1; C29 = 2; C30 = 2);"PL = a";ALS(EN(C27 = 1; C28 = 2; C29 = 1; C30 = 1);"PL = b";ALS(EN(C27 = 1; C28 = 2; C29 = 1; C30 = 2);"PL = a";ALS(EN(C27 = 1; C28 = 2; C29 = 2; C30 = 1);"PL = c";ALS(EN(C27 = 1; C28 = 2; C29 = 2; C30 = 2);"PL = b";ALS(EN(C27 = 2; C28 = 1; C29 = 1; C30 = 1);"PL = c";ALS(EN(C27 = 2; C28 = 1; C29 = 1; C30 = 2);"PL = b";ALS(EN(C27 = 2; C28 = 1; C29 = 2; C30 = 1);"PL = d";ALS(EN(C27 = 2; C28 = 1; C29 = 2; C30 = 2);"PL = c";ALS(EN(C27 = 2; C28 = 2; C29 = 1; C30 = 1);"PL = d";ALS(EN(C27 = 2; C28 = 2; C29 = 1; C30 = 2);"PL = c";ALS(EN(C27 = 2; C28 = 2; C29 = 2; C30 = 1);"PL = e";ALS(EN(C27 = 2; C28 = 2; C29 = 2; C30 = 2);"PL = d";"FOUT"))))))))))))))))

deleted as you solved your issue.
Glad you got it to work for you. Please don't forget to mark the post as solved using the thread tools dropdown at the top of the post.