# Sales Commissions staggered incrementally

#### johneirc

##### New Member
Hi,

I trying to create a commission structure whereby a sales team gets commission based on increasing percentages, but the relevant percentage is only paid on the incremental range. It's best explained by an example.

0 - 15,000 paid at 20%
15,001 - 25,000 paid at 25%
25,001 - 35,000 paid at 30%
35,001 - 40,000 paid at 35%
40,001+ at 40%.

So, if a sales rep had 50,000 sales in the month, the first 15,000 would be paid at 20%, the next 10,000 paid at 25% (25,000 - 15,001), the next 10,000 paid at 30% (35,000 - 25,001) and so on.

I tried using a lookup table but it seems to apply the whole amount at a single percentage only, and using an IF statements and lookup tables just became too complicated.

I have been informed by better users of Excel (although, they did not know how) that it might be best to calculate the above using a function generated in VBA. However I do know where to start, as I have only limited experience in VBA.

All help gratefully accepted.

John

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### Domenic

##### MrExcel MVP
Assuming that A1 contains the sales amount, try...

=SUMPRODUCT(--(A1>{0,15000,25000,35000,40000}),A1-{0,15000,25000,35000,40000},{0.2,0.05,0.05,0.05,0.05})

Hope this helps!

#### Joe4

Here is another way to do it, simply by using a series of Excel functions for each step.

Assuming your amount is in cell A1:
=(MIN(A1,15000)*20%)+(MAX(MIN(A1-15000,10000),0)*25%)+(MAX(MIN(A1-25000,10000),0)*30%)+(MAX(MIN(A1-35000,5000),0)*35%)+(MAX(A1-40000,0)*40%)

Though, Domenic's way is a little cleaner!

#### johneirc

##### New Member
Thank you both for your help.

Kind Regards,
John

#### johneirc

##### New Member
Domenic,

On the method you provided, is it possible to replace the values between { } with cell references which contain the various commission values and percentages? I tried but it comes back with an error.

Regards,
John

#### Domenic

##### MrExcel MVP
Let C1:D5 contain the following table...

Code:
``````0	0.2
15000	0.05
25000	0.05
35000	0.05
40000	0.05``````

Then, use the following formula...

=SUMPRODUCT(--(A1>C1:C5),A1-C1:C5,D1:D5)

Hope this helps!

Thanks Domenic

#### websmile

##### New Member
Just discovered this old thread and applied to my requirements for a volume discount scheme incl. calculator sheet, excellent, many thanks Domenic!

Replies
10
Views
305
Replies
27
Views
497
Replies
9
Views
270
Replies
3
Views
1K
Replies
7
Views
1K

1,195,970
Messages
6,012,609
Members
441,715
Latest member
TTP

### 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.

### Which adblocker are you using?

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

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