+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 18

If Statements with Ranges

  1. #1
    Registered User
    Join Date
    06-12-2013
    Location
    Canada
    MS-Off Ver
    Excel 2016
    Posts
    25

    Question If Statements with Ranges

    Hi there,

    I need to calculate some weighted percentages with the following criteria:
    If X=70% then I multiply 80% of 30%
    If X=75% then I multiply 100% of 30%
    If X=80% then I add 10% to the 30%
    Anything beyond 80% is at most 40%.



    How do I calculate what this percentage will be if say for example, X = 73%? It's expected to be over 80%.
    1. If X<70%
    2. If 70%<=X<75%
    3. If 75%<=X<80%


    if X = 69% it will be less than 24% (less than 80% of 30%)
    if X = 73%, the percentage will be between 24%-30% (between 80% and 100% of 30%), how can I determine how this is scaled?
    if X = 81%, then it will be at most 40%

    Thanks bunch!
    Last edited by TheRevenant; 01-12-2019 at 02:03 AM.

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016
    Posts
    2,057

    Re: If Statements with Ranges

    VLOOKUP can do it
    In order to ask a question you must already know most of the answer. Ask A Foolish Question, Robert Sheckley

  3. #3
    Registered User
    Join Date
    06-12-2013
    Location
    Canada
    MS-Off Ver
    Excel 2016
    Posts
    25

    Re: If Statements with Ranges

    Quote Originally Posted by tim201110 View Post
    VLOOKUP can do it
    Can you give an example?

    if X = 69% it will be less than 24% (less than 80% of 30%)
    if X = 73%, the percentage will be between 24%-30% (between 80% and 100% of 30%), how can I determine how this is scaled?
    if X = 81%, then it will be at most 40%
    Last edited by TheRevenant; 01-12-2019 at 02:03 AM.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    22,213

    Re: If Statements with Ranges

    Will you please attach a sample Excel workbook?

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.
    If anyone has helped you towards solving a problem, then you can thank them by clicking on their reputation star bottom left.

  5. #5
    Registered User
    Join Date
    06-12-2013
    Location
    Canada
    MS-Off Ver
    Excel 2016
    Posts
    25

    Re: If Statements with Ranges

    I first need to know the formula that would calculate these percentages for the conditions I mentioned. The outcomes are stated in the original post.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    22,213

    Re: If Statements with Ranges

    And we need to see the exact set-up and formatting of the data in question to be able to provide an answer, otherwise there will be an element of guesswork. Help us to help you. A very simple sample workbook will do.

  7. #7
    Registered User
    Join Date
    06-12-2013
    Location
    Canada
    MS-Off Ver
    Excel 2016
    Posts
    25

    Re: If Statements with Ranges

    Let me know if this helps.
    Attached Files Attached Files

  8. #8
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    20,388

    Re: If Statements with Ranges

    I didn't look at your sheet, as I think I followed you....

    Try this:


    =MIN(40,B4*(MIN(80,80*A4/70)+MIN(20,4*MAX(0,A4-70))+2*MAX(0,A4-75)))
    Attached Files Attached Files
    Glenn



  9. #9
    Registered User
    Join Date
    06-12-2013
    Location
    Canada
    MS-Off Ver
    Excel 2016
    Posts
    25

    Re: If Statements with Ranges

    Hey Glenn, I think you are on the right track except X=80 should give me 40% (100% of 30% + 10%).

    Anything beyond 80%: 81,82,83,100, should give me at most 40%.
    Last edited by AliGW; 01-13-2019 at 04:10 AM.

  10. #10
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    20,388

    Re: If Statements with Ranges

    Your wording was ambiguous. At 70...80%, at 75... 100% and at 80 (I assumed) 110% (i.e. 33)

    =MIN(40,B4*(MIN(80,80*A4/70)+MIN(20,4*MAX(0,A4-70))+20*MAX(0,A4-75)/3))
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    06-12-2013
    Location
    Canada
    MS-Off Ver
    Excel 2016
    Posts
    25

    Re: If Statements with Ranges

    Quote Originally Posted by Glenn Kennedy View Post
    Your wording was ambiguous. At 70...80%, at 75... 100% and at 80 (I assumed) 110% (i.e. 33)

    =MIN(40,B4*(MIN(80,80*A4/70)+MIN(20,4*MAX(0,A4-70))+20*MAX(0,A4-75)/3))
    Thank you Glenn.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    22,213

    Re: If Statements with Ranges

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  13. #13
    Registered User
    Join Date
    06-12-2013
    Location
    Canada
    MS-Off Ver
    Excel 2016
    Posts
    25

    Re: If Statements with Ranges

    Can you explain the logic behind what you did?

    MIN
    {40,B4*
    [
    MIN(80,80*A4/70)
    Why 80*A4/70?
    Why take the minimum?
    Which 80 corresponds to the factor of 80% multiplied by 30% when it reaches 70%?
    Which 80 corresponds to anything beyond the 80%?

    I should have used different numbers for my example.

    MIN(20,4*MAX(0,A4-70))
    What does the 20 represent?
    Why is there a factor of 4?
    Why the difference

    20*MAX(0,A4-75)/3
    ]
    }
    Why divide by 3?

    See attached.
    Attached Files Attached Files
    Last edited by TheRevenant; 01-13-2019 at 03:47 PM.

  14. #14
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    20,388

    Re: If Statements with Ranges

    The formula has 4 parts, one for each condition:
    =MIN(40,B4*(MIN(80,80*A4/70)+MIN(20,4*MAX(0,A4-70))+20*MAX(0,A4-75)/3))

    Red: If X=70% then I multiply 80% of 30%. multiplies80 by a4 and divids by 70 and returns that value, or 80 - whichever is smaller.

    PLUS Green: If X=75% then I multiply 100% of 30%. In the next bit, an increase of 5 in X produces an increase of 20 (100-80) in the number to be multiplied by the %. This bit is only applied at values above 70. So... return the maximum bethween zero and a4-70. Multiply by 4 (being 20/5. Finally return that value or 20 - whichever is smaller.

    PLUS Blue. If X=80% then I add 10% to the 30%... Same logic, except that your wording was confused here. What you mean was that the END RESULT would rise to 40%, whereas previously you had talked about the value that was multiplied by 30%. So, what you were really saying was that when A4 rises by a further 5, the number multiplied by 30% should invrease by 33.3333%. 20*5/3=33.33333

    Black. then multiply by the percentage (30) and return that value, or 40, whichever is smaller.

    That's it....

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  15. #15
    Registered User
    Join Date
    06-12-2013
    Location
    Canada
    MS-Off Ver
    Excel 2016
    Posts
    25

    Re: If Statements with Ranges

    Thank you!

    If all these cells were formatted as a percentage, the formula will not work.

    What changes does the formula need to take into account cells as percentages and not numbers?

    Otherwise I just get '40' regardless.
    Last edited by TheRevenant; 01-13-2019 at 10:10 PM.

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1