Threshold formula - Page 2
Thanks:  0
Likes:  0

# Thread: Threshold formula

1. ## Re: Threshold formula

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

2. ## Re: Threshold formula

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

M.

3. ## Re: Threshold formula

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

4. ## Re: Threshold formula

Originally Posted by DellNiv
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

 A B C D E 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 Helper1 Helper2 9 0 15 0% 0 0 10 16 30 10% 30 16 11 31 40 15% 40 0 12 41 20% 16 0

B4 = 35

 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

B4 = 50

 A B C D E 1 Profit 1000 2 Starters 1 3 4 Actual starters 50 5 Total Profit 50000 6 Commision: 6500 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 10 12 41 20% 50 10

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

M.

5. ## Re: Threshold formula

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.

6. ## Re: Threshold formula

Originally Posted by 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.
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.

7. ## Re: Threshold formula

Originally Posted by 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.
See if this solution is more in line with what you expected.

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

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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•