Formula for commission structure

ronie85

Board Regular
Joined
Jan 25, 2014
Messages
96
Office Version
  1. 365
Platform
  1. Windows
Hi, I would like a formula for a commission structure.
Say C5 to C16 are months of the year with sales figures per month for which commission is paid. I would like the formula to work out the commission I would get each month.
The end of the formula on months after January will then minus the paid amounts of previous months though I can add this in myself.

The commission structure is below for which I would earn 0% on all sales up to 250,000.00, 1.25% on sales between 250,000.01 to 400,000.00 and so on.

It seems a bit complex and I hope to have it as 1 formula as I am currently using numerous formulas to calculate this.
Your help is greatly appreciated.
 

Attachments

  • Commission Thresholds.PNG
    Commission Thresholds.PNG
    6.6 KB · Views: 12

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Is your commission calculated on each sale of on the cumulative total?
It will help if you post an extract of your information with the forum's tool named XL2BB.
Please show the brackets and rates and the sample result for one month.
I am travelling and creating your sheet will take time.
I have posted solutions to similar questions.
 
Upvote 0
Maybe something like the below (change A1 to the cell reference with the sales total)'

Late Edit because of stupidity on my part

Book1.xlsb
ABCD
12500000
22501001.25
3400000.001875
4575000.004587.5
5500000.003425
6800000.008525
Sheet3
Cell Formulas
RangeFormula
D1:D6D1=SUMPRODUCT(--(A1>{250000;400000;575000;800000}), (A1-{250000;400000;575000;800000}), {0.0125;0.003;0.002;0.0025})


Edit: If not what you require then please answer the questions @Dave Patton has asked in post number 2 (and please [if possible] do use XL2BB as Dave has suggested for posting excerpts from a spreadsheet rather than an image, as we can't copy/paste it into a sheet)
 
Last edited:
Upvote 1
Please specify all the rates that are applicable and what version of Excel that you are using.
 
Upvote 0
Try the alternative(s) that you prefer.

With older versions of Excel, use SumProduct.
E10 uses the Lamba feature of Excel to build the formula. It can be extended to show the monthly amount.

With Name Manager
in New part enter a name for the new function (something relevant); I just used Commission_a.
in value part enter =LAMBDA(CumRev,LET(b, {0;250000;400000;575000;800000}, r, {0;0.0125;0.003;0.002;0.0025}, SUM((CumRev > b) * (CumRev - b) * r)))

The function specifies the necessary parameter "CumRev"

Commissions 2023.xlsm
ABCDEF
1BracketsRates
2
300.00%0.00
4250,0001.25%1,875.00
5400,0001.55%2,712.50
6575,0001.75%3,937.50
7800,0002.00%0.00
81.E+35
9
10Amount800,000.008,525.008,525.008,525.008,525.00
2d
Cell Formulas
RangeFormula
C3:C7C3=MIN($B$10-A3,A4-A3)*B3
A8A8=10^35
C10C10=SUMPRODUCT(--(B10>A3:A7),B10-A3:A7,B3:B7-B2:B6)
D10D10=SUM((B10>A3:A7)*(B10-A3:A7)*(B3:B7-B2:B6))
E10E10=Commission_a(B10)
F10F10=SUM(C3:C7)
 
Upvote 0
Commissions 2023.xlsm
CDE
1
2
3
4SalesCommission
5January180,000.000.00
6February100,000.00375.00
7March150,000.001,965.00
8April100,000.001,550.00
9May100,000.001,660.00
10June120,000.002,100.00
11July100,000.001,875.00
12August100,000.002,000.00
13September125,000.002,500.00
14October250,000.005,000.00
15November100,000.002,000.00
16December100,000.002,000.00
171,525,000.0023,025.00
18
19Total1,525,000.0023,025.00
20
2d
Cell Formulas
RangeFormula
D7D7=D6+50000
E5:E16E5=Commission_a(SUM($D$5:D5))-SUM($E$4:E4)
D17:E17D17=SUM(D5:D16)
D19D19=D17
E19E19=Commission_a(D19)
 
Upvote 0
Commissions 2023.xlsm
CDE
1
2
3
4SalesCommission
5January180,000.000.00
6February100,000.00375.00
7March150,000.001,965.00
8April100,000.001,550.00
9May100,000.001,660.00
10June120,000.002,100.00
11July100,000.001,875.00
12August100,000.002,000.00
13September125,000.002,500.00
14October250,000.005,000.00
15November100,000.002,000.00
16December100,000.002,000.00
171,525,000.0023,025.00
18
19Total1,525,000.0023,025.00
2d
Cell Formulas
RangeFormula
E5:E16E5=Commission_a(SUM($D$5:D5))-SUM($E$4:E4)
D17:E17D17=SUM(D5:D16)
E19E19=Commission_a(D19)
 
Upvote 0
Hi All, thanks for this. @MARK858 the =SUMPRODUCT(--(A1>{250000;400000;575000;800000}), (A1-{250000;400000;575000;800000}), {0.0125;0.003;0.002;0.0025}) works however could you explain why the 1.25%, 1.55%, 1.75% and 2% is in the formula as {0.0125;0.003;0.002;0.0025} ?
Although it works I don't understand it so that I can apply it to future commission structures of different percentages.
 
Upvote 0
Hi All, thanks for this. @MARK858 the =SUMPRODUCT(--(A1>{250000;400000;575000;800000}), (A1-{250000;400000;575000;800000}), {0.0125;0.003;0.002;0.0025}) works however could you explain why the 1.25%, 1.55%, 1.75% and 2% is in the formula as {0.0125;0.003;0.002;0.0025} ?
Although it works I don't understand it so that I can apply it to future commission structures of different percentages.
I get it now, its the differences in the percentages!
 
Upvote 0
@ronie85 although you were looking for a single formula. I believe you would be better off looking at post 5 by @Dave Patton (where he is using a table for the brackets and rates), as it it much easier to maintain if your brackets or commission rates change (as they normally do in my experience)
 
Upvote 0

Forum statistics

Threads
1,223,948
Messages
6,175,571
Members
452,652
Latest member
eduedu

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