Thanks:  0
Likes:  0

1. ## Threshold formula

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.

 Profit 1000 Starters 1 Actual starters 16 16000 Commision: 3194.8 =SUMPRODUCT(--(16000>0;16;31;41),--(16000-0;16;31;41),0;0.1;0.05;0.05) Low Threshold High Threshold Rate Diff.Rate 0 15 0% 0% 16 30 10% 10% 31 40 15% 5% 41 20% 5%

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

2. ## Re: Threshold formula

Not sure i understand what you are trying to do

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%

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.

3. ## Re: Threshold formula

Originally Posted by Marcelo Branco
Not sure i understand what you are trying to do

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%

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

M.

5. ## Re: Threshold formula

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

M.

6. ## Re: Threshold formula

Originally Posted by Marcelo Branco
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

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

7. ## Re: Threshold formula

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})))`

8. ## Re: Threshold formula

Originally Posted by DHayes
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})))`
This doesn't pull through anything, I get an error.

9. ## Re: Threshold formula

I put the formula in B6. My excel is set up different to yours. try now.
Code:
`=SUM((B5)*(LOOKUP(B4,{0,16,31,41},{0,0.1,0.15,0.2})))`

10. ## Re: Threshold formula

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

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.