Urgent Help! Calculating Tiered Commission...Please!

[Luke_E]

Hi all

I am trying to work out a formula for my below commission structure;

£0.00 - £5,000 generated = 0% commission
£5,000 - £10,000 = 15% commission
£10,000 - £15,000 = 17.5% commission
£15,000 - £20,000 = 20% commission
£20,000 - £25,000 = 22.5% commission
£25,000 + = 25% commission

(so, for example, if I were to generate £16,000 for the business, £5,000 would be non commission-able. The next £5,000 would give me £750.00 (15%). The following £5,000 would be £875.00 commission (17.5%) and the final £1,000 would be @ 20% so another £200). I would earn £750 + £875 + £200 (total of £1,825.00).

How would I put this all into one excel formula? Is there a way to do it so I can input the money I have generated into one cell and the commission is automatically generated into another?

Any help would be amazing!

 

[mfexcel]

Check this out. This should be good enough to solve your problem.
Excel Magic Trick 453: VLOOKUP for Commission Brackets Calculation (LOOKUP function also) - YouTube

Hi all

I am trying to work out a formula for my below commission structure;

£0.00 - £5,000 generated = 0% commission
£5,000 - £10,000 = 15% commission
£10,000 - £15,000 = 17.5% commission
£15,000 - £20,000 = 20% commission
£20,000 - £25,000 = 22.5% commission
£25,000 + = 25% commission

(so, for example, if I were to generate £16,000 for the business, £5,000 would be non commission-able. The next £5,000 would give me £750.00 (15%). The following £5,000 would be £875.00 commission (17.5%) and the final £1,000 would be @ 20% so another £200). I would earn £750 + £875 + £200 (total of £1,825.00).

How would I put this all into one excel formula? Is there a way to do it so I can input the money I have generated into one cell and the commission is automatically generated into another?

Any help would be amazing!

 

[Luke_E]

Check this out. This should be good enough to solve your problem.
Excel Magic Trick 453: VLOOKUP for Commission Brackets Calculation (LOOKUP function also) - YouTube

Thanks! I will check it out and let you know if i can make sense of it!

 

[Benders]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]16000
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]5000
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]2
[/TD]
[TD]5000
[/TD]
[TD]10000
[/TD]
[TD]0.15
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]3
[/TD]
[TD]10000
[/TD]
[TD]15000
[/TD]
[TD]0.175
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]4
[/TD]
[TD]15000
[/TD]
[TD]20000
[/TD]
[TD]0.200
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[TD]20000
[/TD]
[TD]25000
[/TD]
[TD]0.225
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]6
[/TD]
[TD]25000
[/TD]
[TD]
[/TD]
[TD]0.250
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]
[/TD]
[TD]Commission
[/TD]
[TD]1825
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

Don't forget to add the 0 in Cells E7 and H7

Formula in Cell D8 is:
=VLOOKUP(IF(IF(B2>5000,5000,0)>0,1,0),E:H,4,0)*(IF(B2-F2>5000,5000,B2-F2))+VLOOKUP(IF(IF(B2-F3>5000,5000,B2-F3)>0,2,0),E:H,4,0)*(IF(B2-F3>5000,5000,B2-F3))+VLOOKUP(IF(IF(B2-(F4)>5000,5000,B2-(F4))>0,3,0),E:H,4,0)*(IF(B2-F4>5000,5000,B2-F4))+VLOOKUP(IF(IF(B2-(F5)>5000,5000,B2-(F5))>0,4,0),E:H,4,0)*(IF(B2-F5>5000,5000,B2-F5))+VLOOKUP(IF(IF(B2-(F6)>5000,5000,B2-(F6))>0,5,0),E:H,4,0)*(IF(B2-F6>5000,5000,B2-F6))+VLOOKUP(IF(IF(B2-(F7)>=5000,5000,B2-(F7))>=5000,6,0),E:H,4,0)*(IF(B2-F7>5000,5000,B2-F7))

I have not looked at the video that has been kindly suggested for this problem. I however spent some time so thought of sharing this with you anyway. I tested this with a few numbers and seems to work OK.

 

[Snakebitten]

Hi, This method works with up to 5 or 6 commission tiers, any more than that and I would create a VBA formula.
Assume that your business generated is in cell C27. So here it is,

=IF(C27-25000>=0,(C27-25000)*0.25+3750,IF(C27-20000>=0,(C27-20000)*0.225+2625,IF(C27-15000>=0,(C27-15000)*0.2+1625,IF(C27-10000>=0,(C27-10000)*0.175+750,IF(C27-5000>=0,(C27-5000)*0.15,0)))))

Cheers!

 

[Luke_E]

Hi, This method works with up to 5 or 6 commission tiers, any more than that and I would create a VBA formula.
Assume that your business generated is in cell C27. So here it is,

=IF(C27-25000>=0,(C27-25000)*0.25+3750,IF(C27-20000>=0,(C27-20000)*0.225+2625,IF(C27-15000>=0,(C27-15000)*0.2+1625,IF(C27-10000>=0,(C27-10000)*0.175+750,IF(C27-5000>=0,(C27-5000)*0.15,0)))))

Cheers!

Thanks everyone...this one works perfectly!!!! Amazing response; really appreciate it!

 

[mfexcel]

Just to remind you... this one is hard-code and won't work if your commission tier / rate changes in the future.

Thanks everyone...this one works perfectly!!!! Amazing response; really appreciate it!

=IF(C27-25000>=0,(C27-25000)*0.25+3750,IF(C27-20000>=0,(C27-20000)*0.225+2625,IF(C27-15000>=0,(C27-15000)*0.2+1625,IF(C27-10000>=0,(C27-10000)*0.175+750,IF(C27-5000>=0,(C27-5000)*0.15,0)))))

Cheers!

 

[mfexcel]

I've been trying to figure out a single formula for this kind of commission calculation, without success...
Suddenly something came to my mind today and I have come up with this...

[TABLE="width: 356"]
<tbody>[TR]
[TD]Start Range[/TD]
[TD]End Range[/TD]
[TD]Commission %[/TD]
[/TR]
[TR]
[TD] -[/TD]
[TD] 5,000[/TD]
[TD="align: right"]0.0%[/TD]
[/TR]
[TR]
[TD] 5,001[/TD]
[TD] 10,000[/TD]
[TD="align: right"]15.0%[/TD]
[/TR]
[TR]
[TD] 10,001[/TD]
[TD] 15,000[/TD]
[TD="align: right"]17.5%[/TD]
[/TR]
[TR]
[TD] 15,001[/TD]
[TD] 20,000[/TD]
[TD="align: right"]20.0%[/TD]
[/TR]
[TR]
[TD] 20,001[/TD]
[TD] 25,000[/TD]
[TD="align: right"]22.5%[/TD]
[/TR]
[TR]
[TD] 25,001[/TD]
[TD] or above[/TD]
[TD="align: right"]25.0%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Revenue[/TD]
[TD] 16,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Commission[/TD]
[TD] 1,825[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

=SUM(LOOKUP(ROW(INDIRECT("1:"&B9)),$A$2:$C$7)) 
CTRL SHIFT ENTER
'Note: the column of End Range is not necessary.  It is put there for better illustration of the range.

where
B9 is the total revenue
A2:C7 is the commission rate table

Two major limitations:
The greatest number that can be computed is limited by the number of rows in the spreadsheet. In Excel 2010, which is 1048576
It doesn't work for decimal numbers. (although it could be modified to fit decimal, it makes the formula longer...)

Last but not least, it create a long array when the number is large, i.e. efficiency suffers.

Any other ideas in creating a single formula for such calculation?