Nested IF Statements. Move levels of nesting than allowed.

The formula below is supposed to return the perfomance rates of my employees. I have read that you're allowed up to 64 levels of nested functions, so why am I getting the error message of more levels of nesting than allowed? If I cannot nest IF statements in this way, is there another way to do this? I have 9 different order types that could be a variable for the value of my B column, I have found that if I only had 8, the formula works perfectly. As soon as I add in the 9th variable, the formula breaks.

=IF(B2=Orders!$A$1,D2/45,IF(B2=Orders!$A$2,D2/10,IF(B2=Orders!$A$3,D2/45,IF(B2=Orders!$A$4,D2/20,IF(B2=Orders!$A$5,D2/100,IF(B2=Orders!$A$6,D2/30,IF(B2=Orders!$A$7,D2/50,IF(B2=Orders!$A$8,D2/50,IF(B2=Orders!$A$9,D2/40,"")))))))))

Perhaps the file you are working on is an .xls file (i.e. compatible with XL2003 and earlier) rather than an .xlsx file. You can save the file as an Excel workbook (i.e. with an .xlsx extension), then close the file and then re-open it, and you should have no problems with nesting to 9 levels.

Another way of doing it would be to set up a small table, say in columns X and Y, where X1 would contain the formula:

=Orders!$A1

which can be copied down to X9, and Y1 to Y9 would contain the divisors in your formula, i.e. 45, 10, 45, 20, 100, 30, 50, 50, 40

Then you can just have this formula:

=IF(COUNTIF($X$1:$X$9,B2),D2/VLOOKUP(B2,$X$1:$Y$9,2,0),"")

and this will be compatible with both versions of the file.

Hope this helps.

Pete

Thanks Pete! You've been a huge help.