+ Reply to Thread
Results 1 to 18 of 18

If Statements with Ranges

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

    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, 2019
    Posts
    2,357

    Re: If Statements with Ranges

    VLOOKUP can do it

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

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    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!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

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

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    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
    35

    Re: If Statements with Ranges

    Let me know if this helps.
    Attached Files Attached Files

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    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




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

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

    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 Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    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
    35

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    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
    35

    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 Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    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
    35

    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.

  16. #16
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: If Statements with Ranges

    If all 3 are formatted as %, then a simple adjusment:

    =MIN(0.4,B4*(MIN(0.8,0.8*A4/0.7)+MIN(0.2,4*MAX(0,A4-0.7))+20*MAX(0,A4-0.75)/3))

    will work

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

    Re: If Statements with Ranges

    I get 0.

    Can you check my attached file?
    Attached Files Attached Files

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: If Statements with Ranges

    Am I beginning to lose my way here?? Your objective seems to have become more general. If I have lost my way, can you re-state your objectives....
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. If Statements and Ranges - Help!
    By abbiegards92 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-21-2016, 01:06 AM
  2. Cell ranges and IF statements
    By stefan82 in forum Excel General
    Replies: 4
    Last Post: 04-24-2012, 07:00 PM
  3. Replies: 2
    Last Post: 02-08-2012, 10:51 PM
  4. If statements for ranges
    By garopro in forum Excel General
    Replies: 11
    Last Post: 09-29-2011, 11:33 AM
  5. Using If/Then/Else statements with ranges of data
    By ThisIsEverything in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-22-2010, 09:08 PM
  6. IF statements and ranges
    By Idz21 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-22-2006, 05:22 PM
  7. Problem with Ranges in IF statements
    By anar_baku in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-21-2005, 06:58 AM

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