Threshold formula
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Threshold formula

  1. #1
    New Member
    Join Date
    Jan 2017
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    14,261
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    4 Thread(s)

    Default 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.
    Last edited by Marcelo Branco; Dec 7th, 2017 at 05:11 AM.

  3. #3
    New Member
    Join Date
    Jan 2017
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Threshold formula

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

  4. #4
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    14,261
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Threshold formula

    I misunderstood your question, re-thinking

    M.

  5. #5
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    14,261
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    4 Thread(s)

    Default 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. #6
    New Member
    Join Date
    Jan 2017
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Threshold formula

    Quote Originally Posted by Marcelo Branco View Post
    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. #7
    Board Regular
    Join Date
    Nov 2014
    Location
    South Africa
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #8
    New Member
    Join Date
    Jan 2017
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Threshold formula

    Quote Originally Posted by DHayes View Post
    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. #9
    Board Regular
    Join Date
    Nov 2014
    Location
    South Africa
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #10
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    14,261
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    4 Thread(s)

    Default 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.
    Last edited by Marcelo Branco; Dec 7th, 2017 at 07:51 AM.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com