# Calculating Commission Structure

#### bobitici

##### New Member
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?

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### Special-K99

##### Well-known Member
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"

Last edited:

#### cunningAce

##### Board Regular
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)

Replies
2
Views
379
Replies
5
Views
593
Replies
5
Views
4K
Replies
2
Views
16K