MORE THAN 8 IF CONDITION IN A FORMULA IS NOT ACCEPTING

[Guest]

Sales (in units) 100 200 300 400 500 600 700 800 900 1000 1100
Commission (per unit) 0 3 4 5 6 7 8 9 10 11
12
above sales slabs and commission per unit has given. if any person sold up
to 100 units commission his comission shoud be 0, if a person sold between
101 to 200 then his commission should be Units * 3, if a persons sold between
201 to 300 then his commission should be Units * 4 like that a person will
get commission on sales.

for the above i want to use a if condition to calculate commission. for that
i have to use 10 if conditions in a formula but it is accepting up to 7 if
conditions only in a formula

please show the solution for the above

 

[Guest]

XL versions before XL 2007 have a limit of 7 nested functions. There are
ways around this limite; see here for details as it applies to IF functions:
http://www.cpearson.com/excel/nested.htm

Dave

 

[Bob Phillips]

=IF(H1<2,0,(H1-1)*100)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[macropod]

=MIN(12,INT((Sales-1)/100)+2)