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:

£0k to £9k	0%	£0.00
£9k - £29999	10%	£2,100.00
£30k - £44999	15%	£2,250.00
£45k - £59999	20%	£3,000.00
£60k+		25%	uncapped

<colgroup><col span="3"><col></colgroup><tbody>
</tbody>

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