Commision Calculation

I have a value in cell A1 (Value = 52250). and need to calculate the comm on this value based on the following criteria:

Commision Scale
0 to R25 000 3.00% R 750.00 (25000 *3%)
25001 to R40 000 4.00% R 1,600.00 (40000 *4%)-if Salesman earns more than 25000, the 3% will fall away, he/she will earn 4% up to 40000, if they earn more than 40000, theyb will earn 4% up to 40000 and the criteria below will then be applicable

40001 to R55 000 6.00% R 900.00 (55000-40000 *6%)
50001 to R70 000 7.50% R 1,125.00 (70000-55000 *7.5%)
70001 to R85 000 8.00% R 1,200.00 (85000-70000 *8%)
85001 to R100 000 8.50% R 1,275.00 ( 100000-85000 *8.5%)
+ R100 000 10.00% of all amounts above 100000

for egif Saleman earns 120000 , he/she will earn 10% x 20000 and the other criteria will then apply for amounts below the 100000


The commision works as follows:

1) If the salesman generates less than 25000 worth of sales, he/she will earn 3% of the sales

2) If the salesman generates more than 25000 worth of sales, he/she will earn comm as follows (the % below 25000 will fall away)

25001 to R40 000 4.00% ,
40001 to R55 000 6.00% of difference between amount greater than 40000- 40000
55001 to R70 000 7.50% of difference between amount greater than 55000 -55000
70001 to R85 000 8.00% of difference between amount greater than 70000 -70000
85001 to R100 000 8.50% of difference between amount greater than 85000 -85000
+ R100 000 10.00% Receive 10% of amounts in Excess of 100000


Attached please find sample data. I need a formula that will calculate the commision based on a sliding scale

Your assistance will be most appreciated

Regards



Howard

http://www.mrexcel.com/forum/showthr...ghlight=howard