Hi,

I am trying to calculate a sales commission using excel, I can do bits of it
but cant get every break point to calculate.

The main variables are :

Sales Target : e.g. 120000
Actual Sales : 140000

Commission is calculated as follows:

Commission starts at 80% of target (96000)

0% commission is paid if sales fail to reach 80% of target.
1% commission is paid on sales from 80-90% of target
1.5% commission is paid on sales from 90-100% of target
after target is reached commission is paid in bands
Target plus 10000 2% of sales
Target plus 10000 - 20000 2.25% of sales
over 20000 2.5%

I will give a example based on the above

1% of 120000 = 120
1.5% of 120000 = 180
+ 10000 = 200
+ 10000 to 20000= 225

Therefore the total commission is 725.

Thanks in advance

Colin Thornton