Threshold formula

[DellNiv]

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

 

[DellNiv]

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.

sorry that does not work either, I cannot change the starters for it to pick up the next threshold. for example if there is 50 starters next month I'll have to do another formula again. The point of this is that the thresholds should be interchangeable and the starters should be too and the one formula should be left as it is.

 

[Marcelo Branco]

You can change the value of B4 to 50 and the result (B6) will be 6500. Isn't it what you want?

M.

 

[DellNiv]

Sorry this does not work still? When I change the value of B4 the value of B6 stays the same

 

[Marcelo Branco]

Sorry this does not work still? When I change the value of B4 the value of B6 stays the same

It worked perfectly for me

B4 = 16

[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]

16​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Total Profit​

[/TD]
[TD]

16000​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Commision:​

[/TD]
[TD]

1600​

[/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]

16​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

31​

[/TD]
[TD]

40​

[/TD]
[TD]

15%​

[/TD]
[TD]

40​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

41​

[/TD]
[TD][/TD]
[TD]

20%​

[/TD]
[TD]

16​

[/TD]
[TD]

0​

[/TD]
[/TR]
</tbody>[/TABLE]


B4 = 35

[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]


B4 = 50

[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]

50​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Total Profit​

[/TD]
[TD]

50000​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Commision:​

[/TD]
[TD]

6500​

[/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]

10​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

41​

[/TD]
[TD][/TD]
[TD]

20%​

[/TD]
[TD]

50
​

[/TD]
[TD]

10​

[/TD]
[/TR]
</tbody>[/TABLE]


Check if you have used exactly the formulas I've suggested in post 10

M.

 

[DellNiv]

The sumproduct doesn't even link into the amount of actual starters?

I can do the if formula but it's too long I was hoping for a sum product to work.

 

[Marcelo Branco]

The sumproduct doesn't even link into the amount of actual starters?

I can do the if formula but it's too long I was hoping for a sum product to work.

I don't understand what you're saying.
The SUMPRODUCT (B6) uses the values in C9:C12 (given Rates) and the calculated values in Helper2 column (E9:E12) that are linked with B4 (amount of starters).
It's a very short formula
=SUMPRODUCT(C9:C12,E9:E12)*B1

M.

 

[Marcelo Branco]

The sumproduct doesn't even link into the amount of actual starters?

I can do the if formula but it's too long I was hoping for a sum product to work.

See if this solution is more in line with what you expected.


[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]

Min (Trigger)​

[/TD]
[TD]

15​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Actual starters​

[/TD]
[TD]

16​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Total Profit​

[/TD]
[TD]

16000​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Commision:​

[/TD]
[TD]

1600​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

Low Threshold​

[/TD]
[TD]

High Threshold​

[/TD]
[TD]

Rate​

[/TD]
[TD]

Marginal Rate​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

1​

[/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]


Criteria in B1:B3

Formula in B6
=B1*B5

Formula in B7
=IF(B5>B3,SUMPRODUCT(--(B5>=A10:A12),B5+1-A10:A12,D10:D12)*B1,0)

M.