bobitici

New Member
Joined
Mar 2, 2018
Messages
1
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 £9k0%£0.00
£9k - £2999910%£2,100.00
£30k - £4499915%£2,250.00
£45k - £5999920%£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!
 

Some videos you may like

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
Joined
Nov 7, 2006
Messages
8,350
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
Joined
Dec 21, 2017
Messages
86
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,168
Messages
5,570,621
Members
412,333
Latest member
hectamuptra
Top