Threshold formula

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.

Profit1000
Starters1
Actual starters16
16000
Commision: 3194.8 =SUMPRODUCT(--(16000>0;16;31;41),--(16000-0;16;31;41),0;0.1;0.05;0.05)
Low ThresholdHigh Threshold RateDiff.Rate
0150%0%
163010%10%
314015%5%
4120%5%

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Not sure i understand what you are trying to do :confused:

See if this does what you need


A
B
C
D
1
Profit​
1000​
2
Starters​
1​
3
4
Actual starters​
16​
5
Total Profit​
16000​
6
Commision:​
1600​
7
8
Low Threshold​
High Threshold​
Rate​
Diff.Rate​
9
0​
15​
0%​
0%​
10
16​
30​
10%​
10%​
11
31​
40​
15%​
5%​
12
41​
20%​
5%​

<tbody>
</tbody>


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
Not sure i understand what you are trying to do :confused:

See if this does what you need


A
B
C
D
1
Profit​
1000​
2
Starters​
1​
3
4
Actual starters​
16​
5
Total Profit​
16000​
6
Commision:​
1600​
7
8
Low Threshold​
High Threshold​
Rate​
Diff.Rate​
9
0​
15​
0%​
0%​
10
16​
30​
10%​
10%​
11
31​
40​
15%​
5%​
12
41​
20%​
5%​

<tbody>
</tbody>


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.

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

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


A
B
C
D
E
1
Profit​
1000​
2
Starters​
1​
3
4
Actual starters​
35​
5
Total Profit​
35000​
6
Commision:​
3750​
7
8
Low Threshold​
High Threshold​
Rate​
Helper1​
Helper2​
9
0​
15​
0%​
0​
0​
10
16​
30​
10%​
30​
30​
11
31​
40​
15%​
40​
5​
12
41​
20%​
35​
0​

<tbody>
</tbody>


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

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

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