Calculating Commission Structure

[bobitici]

Hello I am trying to create an excel table to calculate a commission structure.

Here is the structure that I am working with:


[TABLE="width: 263"]
<colgroup><col span="3"><col></colgroup><tbody>[TR]
[TD="colspan: 2"]£0k to £9k[/TD]
[TD]0%[/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD="colspan: 2"]£9k - £29999[/TD]
[TD]10%[/TD]
[TD="align: right"]£2,100.00[/TD]
[/TR]
[TR]
[TD="colspan: 2"]£30k - £44999[/TD]
[TD]15%[/TD]
[TD="align: right"]£2,250.00[/TD]
[/TR]
[TR]
[TD="colspan: 2"]£45k - £59999[/TD]
[TD]20%[/TD]
[TD="align: right"]£3,000.00[/TD]
[/TR]
[TR]
[TD]£60k+[/TD]
[TD][/TD]
[TD]25%[/TD]
[TD]
uncapped

[/TD]
[/TR]
</tbody>[/TABLE]

The difficulty that I am finding is because anything from 0£ to £9000 gives 0% commission. Then anything value billed from £9000 to £29999 will give a commission of 10%, etc.

So for example if £34k is billed this would give a commission of £0 + £2100 + £600 = £2700 total.

Can anyone give me the formula that I should use that would mean just entering a value in a cell, and it being calculated automatically?

Thanks in advance!

 

[Special-K99]

I havent worked on this sort of thing in Excel before but try searching on the forum or Googling for "Tiered commission" or "Tiered structure"

 

[cunningAce]

Try this formula,

Sales Total in F1
Lower sales boundaries = A2:A6
Upper sales boundaries = B2:B6
Commission %'s = C2:36

=(MAX(0,(MIN(B2,$F$1)-A2))*C2)+(MAX(0,(MIN(B3,$F$1)-A3))*C3)+(MAX(0,(MIN(B4,$F$1)-A4))*C4)+(MAX(0,(MIN(B5,$F$1)-A5))*C5)+(MAX(0,(MIN(B6,$F$1)-A6))*C6)

 

[AhoyNC]

You might want to look at these links on commissions using Excel.

www.mcgimpsey.com/excel/variablerate.html

https://www.youtube.com/watch?v=fvSnFEsn_NI
https://www.youtube.com/watch?v=bfUIwZ7apkk