Threshold formula - Page 2
Attend Excelapalooza
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Thread: Threshold formula

  1. #11
    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
    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.
    Last edited by DellNiv; Dec 7th, 2017 at 08:35 AM.

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

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

    Default Re: Threshold formula

    Sorry this does not work still? When I change the value of B4 the value of B6 stays the same
    Last edited by DellNiv; Dec 8th, 2017 at 05:00 AM.

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

    Default Re: Threshold formula

    Quote Originally Posted by DellNiv View Post
    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. #15
    New Member
    Join Date
    Jan 2017
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Default Re: Threshold formula

    Quote Originally Posted by DellNiv View Post
    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.
    Last edited by Marcelo Branco; Dec 8th, 2017 at 07:58 AM.

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

    Default Re: Threshold formula

      
    Quote Originally Posted by DellNiv View Post
    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.
    Last edited by Marcelo Branco; Dec 8th, 2017 at 12:58 PM.

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