Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Conditional Formula || IF AND HELP!!!

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    C Hawk
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If net margin is equal to or greater than 1.5% and less than 1.74% then commission is 6.0%, if margin is greater than or equal to 1.75% and less than 1.99% then commission is 6.5% and so on...

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,312
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hiya,

    A couple questions:

    What if Net Margin is <1.5% (commission = 0%?)

    How far does "and so on" go? Does it follow the same trend of for +0.5% for every +0.25% Net Margin to infinity?

    Adam

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-01 14:10, investr wrote:
    If net margin is equal to or greater than 1.5% and less than 1.74% then commission is 6.0%, if margin is greater than or equal to 1.75% and less than 1.99% then commission is 6.5% and so on...
    Vlookup perhaps...

    RET79

  4. #4
    New Member
    Join Date
    Apr 2002
    Location
    C Hawk
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes, less than 1.5 is 0. You don't get paid! And the "so on" part is as follows

    Net Margin 1.50-1.74% = 6% Commission
    Net Margin 1.75-1.99% = 6.5% Commission
    Net Margin 2.00-2.49% = 7.0% Commission

    Up to 11% commission



  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,312
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hiya,

    I'm guessing that was a typo in the last net margin entry:

    Net Margin 1.50-1.74% = 6% Commission
    Net Margin 1.75-1.99% = 6.5% Commission
    Net Margin 2.00-2.49% = 7.0% Commission
    If I get the specifics correctly, a Net margin >= 3.75% equates to the max Commission of 11%

    If this is the case and A2 holds the Net Margin:

    =IF(A2<1.5%,0,MIN(6%+2*(FLOOR(A2,0.0025)-1.5%),11%))

    Hope that helps,
    Adam


  6. #6
    New Member
    Join Date
    Apr 2002
    Location
    C Hawk
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    YOUR AMAZING!

    Thanks Adam , You rock!

  7. #7
    New Member
    Join Date
    Apr 2002
    Location
    C Hawk
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    As long as I am here...

    Goal 95% Paid 100%
    Goal 94% Paid 94%
    Goal 93% Paid 93%

    All the way down. so 95% > = 100 Paid

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,312
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey again,

    Could you specify with a few more details onn that last post? I'm not catching the correlation beween Goal and Paid as well as what happens at the extremes.

    (I apologize if it's an obvious connection - it's 6PM in Florida and my brain is beginning to short circuit).

    Adam

  9. #9
    Board Regular zacemmel's Avatar
    Join Date
    Apr 2002
    Location
    Redmond, WA
    Posts
    636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Be careful with these if statements. You can only have 6 nested if's before you break Excel.

  10. #10
    New Member
    Join Date
    Apr 2002
    Location
    C Hawk
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok, so I want to have a data cell open where I can plug in..lets say 95% under the header of "GOAL", I would like the column next to it to represent a payout based on that variable. So if my Goal is equated by being higher than 95% I would get a 100% payout, and Goal of 94% would get a 94% payout, Goal of 93% would get 93%. As you can see, anything less than 94% gets EVEN payout.

    Basically I just need to learn a formula than can have 5 sets or so of criteria.

    IF a2>= 95% then payout = 100% and if a2=94% then payout = 94%...etc

    I just need to learn how to script what I am thinking in my head LOL

    Thanks adam.

Some videos you may like

User Tag List

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
  •