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!
 

mfexcel

Well-known Member
Joined
Jan 8, 2009
Messages
791
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!
 

Benders

Board Regular
Joined
Mar 18, 2014
Messages
75
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:

Snakebitten

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

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

Well-known Member
Joined
Jan 8, 2009
Messages
791
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

Well-known Member
Joined
Jan 8, 2009
Messages
791
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?
 

Forum statistics

Threads
1,082,447
Messages
5,365,581
Members
400,840
Latest member
Fortune

Some videos you may like

This Week's Hot Topics

Top