# Urgent Help! Calculating Tiered Commission...Please!

#### Luke_E

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

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### mfexcel

##### Well-known Member
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
 B C D E F G H 1 16000 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

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
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
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
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 Range End Range Commission % - 5,000 0.0% 5,001 10,000 15.0% 10,001 15,000 17.5% 15,001 20,000 20.0% 20,001 25,000 22.5% 25,001 or above 25.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?

Replies
7
Views
174
Replies
3
Views
204
Replies
3
Views
602
Replies
22
Views
1K
Replies
3
Views
62