Sliding commission scale - help!

kr4

New Member
Joined
Jun 16, 2023
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi everyone,

I am a recruiter and have a tiered commission system. We have 3 tiers, 10%, 20% and 30%. We have "deals" which is where you add something to your billings.


What tier your commission for each "deal" comes to, is based on what your total is for the quarter so far. For example:

If your quarterly total is below £30,000, any "deal" you put on is 10% commission (For example at the start of the quarter putting on £28,000 would give you £2800)

If your quarterly total lands between £30,000 and £50,000, the proportion of any "deal" you put on that sits within this £30k to £50k bracket is 20% commission. (For example, if I was on £28,000 and then added an extra £10,000 "deal", £2,000 of this would be 10% commission and then the remaining £8000 would be 20% commission (giving me a total of £1800 commission for that "deal")

If your quarterly total is anything above £50,000 for the quarter, then the proportion of any "deal" you put on that is above the £50k bracket is 30% commission. (For example, if I was on £38,000 and then added an extra £20,000 "deal", £12,000 of this would be 20% commission and then the remaining £8000 would be 30% commission (giving me a total of £4,800 for that "deal")

My aim is to have a system where I can note each "deal" one by one, in a column that adds up my total billings to show my total for the quarter, but then for it to work out how much commission I will get in a separate column from each "deal" based on the information above!

Any help at all would be amazing!

Thanks,
Kiran
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Commissions 2023.xlsm
ABCD
1Commission
2Total Deals58,000
3Total 9,4009,4009,400
4
5Deal123
6Amount28,000.0010,000.0020,000.00
7Commission2,800.001,800.004,800.00
8 cumulative2,800.004,600.009,400.00
9
10BracketRate
11
12010%
1330,00020%
1450,00030%
2b
Cell Formulas
RangeFormula
B3B3=SUM((B2>A12:A14)*(B2-A12:A14)*(B12:B14-B11:B13))
C3C3=SUM((B2>{0;30000;50000})*(B2-{0;30000;50000})*({0.1;0.1;0.1}))
D3D3=Rev_r(B2)
B7:D7B7=Rev_r(SUM($B$6:B6))-SUM($A$7:A7)
B8:D8B8=Rev_r(SUM($B$6:B6))


I show 3 versions/alternatives of the same formula; you can try each and use the one that you prefer.
Rev_r is the Lambda version. It shows the parameter CumulDeals that you enter.

With Name Manager
new Rev_r
value =LAMBDA(CumulDeals,LET(u,CumulDeals,b, {0;30000;50000},r, {0.1;0.1;0.1}, SUM((u> b) * (u- b) * r)))
 
Last edited:
Upvote 0
Commissions 2023.xlsm
ABCDEFG
15or
16DealAmountCommissionCumulativeCumulativeCumulative
17128,000.002,800.002,800.002,800.0028,000.002,800.00
18210,000.001,800.004,600.004,600.0038,000.004,600.00
19320,000.004,800.009,400.009,400.0058,000.009,400.00
2045,000.001,500.0010,900.0010,900.0063,000.0010,900.00
2b
Cell Formulas
RangeFormula
C17:C20C17=Rev_r(SUM($B$17:B17))-SUM($C$16:C16)
D17:D20D17=C17+N(D16)
E17:E20E17=Rev_r(SUM($B$17:B17))
F17:F20F17=SCAN(0,B17:B20,LAMBDA(s,c,s+c))
G17:G20G17=Rev_r(F17)
Dynamic array formulas.
 
Upvote 0
Hi everyone,

I am a recruiter and have a tiered commission system. We have 3 tiers, 10%, 20% and 30%. We have "deals" which is where you add something to your billings.


What tier your commission for each "deal" comes to, is based on what your total is for the quarter so far. For example:

If your quarterly total is below £30,000, any "deal" you put on is 10% commission (For example at the start of the quarter putting on £28,000 would give you £2800)

If your quarterly total lands between £30,000 and £50,000, the proportion of any "deal" you put on that sits within this £30k to £50k bracket is 20% commission. (For example, if I was on £28,000 and then added an extra £10,000 "deal", £2,000 of this would be 10% commission and then the remaining £8000 would be 20% commission (giving me a total of £1800 commission for that "deal")

If your quarterly total is anything above £50,000 for the quarter, then the proportion of any "deal" you put on that is above the £50k bracket is 30% commission. (For example, if I was on £38,000 and then added an extra £20,000 "deal", £12,000 of this would be 20% commission and then the remaining £8000 would be 30% commission (giving me a total of £4,800 for that "deal")

My aim is to have a system where I can note each "deal" one by one, in a column that adds up my total billings to show my total for the quarter, but then for it to work out how much commission I will get in a separate column from each "deal" based on the information above!

Any help at all would be amazing!

Thanks,
Kiran


Have you resolved your problem? I'm a student at university with an academic assignment to finish, but I don't have a lot of time. I searched online for help, and I came across Navigating the World of MBA Essay Help as a Busy Student site, which instructs me on how to write an essay and enables me to finish it quickly with the aid of the examples I can see on the website.
Have you resolved your problem?
 
Upvote 0
Welcome to the forum.

You can try the suggested solutions.
Click on the icon below the f(x) in the post's heading, then move to a clean sheet and paste.
or
You can post a clear explanation of your question in a new thread.

or you can post your suggestion

 
Upvote 0
To ErwinHenninger
Since you have an older version of Excel, you would use SumProduct or array enter the sum formula.
Commissions 2023.xlsm
ABE
1Total Deals58,000
2Total Commission9,4009,400
3
2b
Cell Formulas
RangeFormula
B2B2=SUM((B1>A11:A13)*(B1-A11:A13)*(B11:B13-B10:B12))
E2E2=SUMPRODUCT(--(B1>A11:A13),(B1-A11:A13)*(B11:B13-B10:B12))
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

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