Threshold formula

D

DellNiv

New Member
Joined
Jan 30, 2017
Messages
18
Hi,

I am attempting to use a Sumproduct Froumla to work out commission but it isn't quite working.

The below is what I am attempting.

[TABLE="width: 300"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Profit[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Starters[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Actual starters[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]16000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Commision: [/TD]
[TD="align: right"]3194.8 [/TD]
[TD] =SUMPRODUCT(--(16000>0;16;31;41),--(16000-0;16;31;41),0;0.1;0.05;0.05)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Low Threshold[/TD]
[TD]High Threshold[/TD]
[TD] Rate[/TD]
[TD]Diff.Rate[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]15[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]30[/TD]
[TD]10%[/TD]
[TD]10%[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]40[/TD]
[TD]15%[/TD]
[TD]5%[/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD][/TD]
[TD]20%[/TD]
[TD]5%[/TD]
[/TR]
</tbody>[/TABLE]

Profit for 1 person is 1000. 0-15 0% commission. 16-30 10% - so the commision should be 1600 for 16 people hired but my formula is pulling through 3194.8. Then a further 5% for any over 30. and so on.

Hope this makes sense.

Let me know if it doesn't.

Thanks,
Dell
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Not sure i understand what you are trying to do :confused:

See if this does what you need


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Profit​
[/TD]
[TD]
1000​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
Starters​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
Actual starters​
[/TD]
[TD]
16​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
Total Profit​
[/TD]
[TD]
16000​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
Commision:​
[/TD]
[TD]
1600​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
Low Threshold​
[/TD]
[TD]
High Threshold​
[/TD]
[TD]
Rate​
[/TD]
[TD]
Diff.Rate​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
0​
[/TD]
[TD]
15​
[/TD]
[TD]
0%​
[/TD]
[TD]
0%​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD]
16​
[/TD]
[TD]
30​
[/TD]
[TD]
10%​
[/TD]
[TD]
10%​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD]
31​
[/TD]
[TD]
40​
[/TD]
[TD]
15%​
[/TD]
[TD]
5%​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12​
[/TD]
[TD]
41​
[/TD]
[TD][/TD]
[TD]
20%​
[/TD]
[TD]
5%​
[/TD]
[/TR]
</tbody>[/TABLE]


Formula in B5 (Total Profit)
=B1*B4

Formula in B6 (Commission)
=SUMPRODUCT(--(B$4>=A$9:A$12),B$4+1-A$9:A$12,D$9:D$12*B$5)

Hope this helps

M.
 
Last edited:
Upvote 0
Marcelo Branco said:
Not sure i understand what you are trying to do :confused:

See if this does what you need


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Profit​
[/TD]
[TD]
1000​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
Starters​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
Actual starters​
[/TD]
[TD]
16​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
Total Profit​
[/TD]
[TD]
16000​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
Commision:​
[/TD]
[TD]
1600​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
Low Threshold​
[/TD]
[TD]
High Threshold​
[/TD]
[TD]
Rate​
[/TD]
[TD]
Diff.Rate​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
0​
[/TD]
[TD]
15​
[/TD]
[TD]
0%​
[/TD]
[TD]
0%​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD]
16​
[/TD]
[TD]
30​
[/TD]
[TD]
10%​
[/TD]
[TD]
10%​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD]
31​
[/TD]
[TD]
40​
[/TD]
[TD]
15%​
[/TD]
[TD]
5%​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12​
[/TD]
[TD]
41​
[/TD]
[TD][/TD]
[TD]
20%​
[/TD]
[TD]
5%​
[/TD]
[/TR]
</tbody>[/TABLE]


Formula in B5 (Total Profit)
=B1*B4

Formula in B6 (Commission)
=SUMPRODUCT(--(B$4>=A$9:A$12),B$4+1-A$9:A$12,D$9:D$12*B$5)

Hope this helps

M.
Click to expand...

Hi,

Thank you for the response. This works only for 16. Once I enter 17 and profit changes to 17000 the commission doubles to 3200 but it should be 1700.

So for the first 0-15 starters 0 commission is paid. from 16 to 30 10% commission. From 31 there is 15% paid.

For example 35 starters =

1st threshold - 30 starters = $30,000 * 10% = $3000 Commission
2nd threshold 5 starters = $5000 * 15% = $750 commission

Total commission payable $3750

Hope this helps,
Dell
 
Upvote 0
You said "first 15 starters 0 commission", so i don't understand your logic for 35000
Shouldn't it be?
15000*0% = 0
15000*10% = 1500
5000*15% = 750
Total: 2250

Still confused :confused:

M.
 
Upvote 0
Marcelo Branco said:
You said "first 15 starters 0 commission", so i don't understand your logic for 35000
Shouldn't it be?
15000*0% = 0
15000*10% = 1500
5000*15% = 750
Total: 2250

Still confused :confused:

M.
Click to expand...

Once it hits over 15 it activates commission... So if 16 then commission is based for all of 16 but if only 15 then no commission..
 
Upvote 0
Hi,
I Hope I understand. give this one a shot
Code: 
=SUM((B5)*(LOOKUP(B4;{0;16;31;41};{0;0,1;0,15;0,2})))
 
Upvote 0
I put the formula in B6. My excel is set up different to yours. try now.
Code: 
[COLOR=#333333][I]=SUM((B5)*(LOOKUP(B4,{0,16,31,41},{0,0.1,0.15,0.2})))[/I][/COLOR]
 
Upvote 0
Maybe something like this


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Profit​
[/TD]
[TD]
1000​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
Starters​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
Actual starters​
[/TD]
[TD]
35​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
Total Profit​
[/TD]
[TD]
35000​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
Commision:​
[/TD]
[TD]
3750​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
Low Threshold​
[/TD]
[TD]
High Threshold​
[/TD]
[TD]
Rate​
[/TD]
[TD]
Helper1​
[/TD]
[TD]
Helper2​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
0​
[/TD]
[TD]
15​
[/TD]
[TD]
0%​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD]
16​
[/TD]
[TD]
30​
[/TD]
[TD]
10%​
[/TD]
[TD]
30​
[/TD]
[TD]
30​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD]
31​
[/TD]
[TD]
40​
[/TD]
[TD]
15%​
[/TD]
[TD]
40​
[/TD]
[TD]
5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12​
[/TD]
[TD]
41​
[/TD]
[TD][/TD]
[TD]
20%​
[/TD]
[TD]
35​
[/TD]
[TD]
0​
[/TD]
[/TR]
</tbody>[/TABLE]


Formula in D9 copied down (Helper1)
=IF(B9="",B$4,IF(B9<=15,0,B9))

Formula in E9 copied down (Helper2)
=IF(B$4<=15,0,MAX(0,MIN(B$4-SUM(E$8:E8),D9-N(D8))))

Result in B6
=SUMPRODUCT(C9:C12,E9:E12)*B1

M.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Copy only Value not Formula to new Cell location after ActiveX Macro button pushed.
Replies
2
Views
168
VinnyDoesntKnowExcelCode
V
F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
124
flashgordie
F
B
  • Question Question
How to make this IF formula work?
Replies
1
Views
252
Fluff
Fluff
W
  • Solved
Fiscal Quarter Percentage difference
2 3
Replies
22
Views
1K
Walker_Ice
W
D
  • Question Question
Cannot make formula SPILL down
Replies
3
Views
573
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

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