Urgent Help! Calculating Tiered Commission...Please!

Luke_E

New Member
Joined
Apr 23, 2014
Messages
3
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!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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!
 
Upvote 0
B
C
D
E
F
G
H
116000


1
0
5000
0
2



2
5000
10000
0.15
3


3
10000
15000
0.175
4


4
15000
20000
0.200
5


5
20000
25000
0.225
6


6
25000

0.250
7


0


0
8
Commission
1825





<tbody>
</tbody>

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.
 
Last edited:
Upvote 0
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!
 
Upvote 0
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!
 
Upvote 0
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!
 
Upvote 0
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...

Start RangeEnd RangeCommission %
- 5,0000.0%
5,001 10,00015.0%
10,001 15,00017.5%
15,001 20,00020.0%
20,001 25,00022.5%
25,001 or above25.0%
Total Revenue 16,000
Total Commission 1,825

<tbody>
</tbody>

Rich (BB code):
=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?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top