IF(AND(...)) question

[ranl]

Hi.
I'm using this function:

=IF(AND(C13=0,B13=0),0,IF(AND(C13=1,B13=0),1,IF(AND(C13=2,B13=0),2,IF(AND(C13=1,B13=1),1,IF(AND(C13=2,B13=1),"Unknown",IF(AND(C13=0,B13=2),2,IF(AND(C13=0,B13=1),1,2)))))))

currently, raws "B" and "C" have the values 0,1,2.
This function works fine on raw "A".
The problem occures when I'm adding my final "IF" - as always, just before the "UK", so it looks like this:

=IF(AND(C22=0,B22=0),0,IF(AND(C22=1,B22=0),1,IF(AND(C22=2,B22=0),2,IF(AND(C22=1,B22=1),1,IF(AND(C22=2,B22=1),"Unknown",IF(AND(C22=0,B22=2),2,IF(AND(C22=0,B22=1),1,if(and(C22=1,B22=2),"Unknown",2))))))))

I keep getting an error: "The formula you typed contains an error".



Is there an "AND" usage limit?
any ideas?

Thanks.

 

[Juan Pablo González]

Moved to Excel questions...

 

[Mark W.]

Excel only supports 7 levels of nesting.

 

[rrdonutz]

... and you could combine your first 3 IF clauses into 1 by typing:

IF(B13=0,C13)

thereby reducing the number of IFs to 6. Further simplification might be possible.

 

[bmacias]

Take a look at the Index to 485 articles at the bottom of the main page.
Then go to the Excel Formulas section. You should be able to find a workaround for the & level limitation.

Good luck.

Ben

 

[Mark W.]

=VLOOKUP(SUMPRODUCT(B22:C22,{10,1}),{0,0;1,1;2,2;10,1;11,1;12,"Unknown";20,2;21,"Unknown";22,2},2,0)

 

[gaz_chops]

Look at Aladin's response to me earlier query "Nested if statements", think it's what your after.

 

[gaz_chops]

Ignore me, didn't read question properly?

 

[ranl]

=VLOOKUP(SUMPRODUCT(B22:C22,{10,1}),{0,0;1,1;2,2;10,1;11,1;12,"Unknown";20,2;21,"Unknown";22,2},2,0)

Many Thanks - That helped a lot.

 

[Aladin Akyurek]

=IF(COUNTIF(B22:C22,0),MAX(B22:C22),IF(SUM(B22:C22)<=2,MIN(B22:C22),"Unknown"))

It will also result in "Unknown" when B22=2 and C22=2.