+ Reply to Thread
Results 1 to 10 of 10

How to - If A is greater than X but less than Y, return Z

  1. #1
    Registered User
    Join Date
    08-13-2019
    Location
    Louisiana
    MS-Off Ver
    excel
    Posts
    50

    How to - If A is greater than X but less than Y, return Z

    How to write a formula that says:

    =IF(L25<8,8,IF(L25>8 but less than 10,10,IF(L25>10 but less than 12,12,0)))

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,602

    Re: How to - If A is greater than X but less than Y, return Z

    Try it like this:


    =IF(L25<8,8,IF(L25<10,10,IF(L25<12,12,0)))

    Hope this helps.

    Pete

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: How to - If A is greater than X but less than Y, return Z

    How about =MAX(8,CEILING(L25,2))?

    Or a lookup function based on a lookup table sorted in descending order.
    Lookup table:
    Please Login or Register  to view this content.
    Then the formula could be =INDEX(2nd_column_lookup_table,MATCH(L25,1st_column_lookup_table,-1)). To handle the cases when L25 is greater than 12, nest inside of an IFERROR() or IFNA() function =IFERROR(INDEX(...),0).

    Will something like that work?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    08-13-2019
    Location
    Louisiana
    MS-Off Ver
    excel
    Posts
    50

    Re: How to - If A is greater than X but less than Y, return Z

    I thought about it for awhile and came up with this formula, which worked until I added my last if argument. Now it says there's a problem with the formula but no clue what the mistake is.

    =IF(L25<5,10,IF(AND(L25>5,L25<=6,12,IF(AND(L25>6,L25<=8,8,IF(AND(L25>8,L25<=10),10,IF(AND(L25>10,L25<=12),12,0)))))

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: How to - If A is greater than X but less than Y, return Z

    for yours you are missing a couple parentheses here ...
    =IF(L25<5,10,IF(AND(L25>5,L25<=6),12,IF(AND(L25>6,L25<=8),8,IF(AND(L25>8,L25<=10),10,IF(AND(L25>10,L25<=12),12,0)))))
    Now, you don't need all those AND statements, if(L25<5,10,IF(L25<=6,12,IF(L25<=8,8
    the point is that each statement is in and of itself enough, for example less than 5 will go first, 5 to 6 will go next, 6.0001 to 8 next, IF/THEN statements work from left to right and work until they find something that satisfies them, that is why Pete gave you what he gave you. So all those ANDs are not necessary.
    Hope that helps.

    Oh, and you aren't dealing with 5 in L25, you have less than 5 and greater than 5 and less than or equal to 6 so a 5 will return zero.
    Last edited by Sam Capricci; 12-11-2019 at 09:13 PM.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  6. #6
    Registered User
    Join Date
    08-13-2019
    Location
    Louisiana
    MS-Off Ver
    excel
    Posts
    50

    Re: How to - If A is greater than X but less than Y, return Z

    Sambo kid,

    Thank you very much. Those 2 parentheses worked like a charm. Can't believe I missed that, but by the time I sent that last post I had been staring at formulas for 6 hours, about brain dead.

    Concerning your subsequent explanation, I want to make sure I understand it correctly. Formulas read one argument at a time from left to right. So when it comes to the first argument that is true, it returns the correct result and does not continue to check the other arguments. Therefore, if I leave out the "between this and that" arguments from my formula and just say "if less than this", and L25 equals 4, the formula will not recognize that all arguments are true and throw up an error message. It will return the result for the first argument. Is that an accurate?

  7. #7
    Registered User
    Join Date
    08-13-2019
    Location
    Louisiana
    MS-Off Ver
    excel
    Posts
    50

    Re: How to - If A is greater than X but less than Y, return Z

    MrShorty,

    Thank you very much for your answer, but those formulas are a little above my pay grade. Not up to speed on the lookup tables.

  8. #8
    Registered User
    Join Date
    08-13-2019
    Location
    Louisiana
    MS-Off Ver
    excel
    Posts
    50

    Re: How to - If A is greater than X but less than Y, return Z

    Quote Originally Posted by Pete_UK View Post
    Try it like this:


    =IF(L25<8,8,IF(L25<10,10,IF(L25<12,12,0)))

    Hope this helps.

    Pete
    Pete,

    Thank you very much for your response. At first, I didn't think your formula would work for what I needed because each argument didn't give a range of "between this and that". For instance, I thought if L25 equaled say 5, the formula would find all arguments true and return an error. But Sambo kid explained that the formula reads left to right and stops on the first one that is true. Now, I understand why your suggested formula would in fact work. Thank you again for your help.

  9. #9
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: How to - If A is greater than X but less than Y, return Z

    lasc to post #6, yes that is correct so for correct results you don’t want to start less than 12 then 10 because even a 4 will be less than 12 so that would satisfy the formula but return a result you don’t want so you usually make them progressive from the left.

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,602

    Re: How to - If A is greater than X but less than Y, return Z

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Pete

+ 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. Replies: 7
    Last Post: 12-06-2015, 05:40 PM
  2. Return Last Value that is greater than 0 in a row
    By Kaas in forum Excel General
    Replies: 7
    Last Post: 08-19-2015, 03:23 AM
  3. [SOLVED] find value which is greater than in a range of cells and return the greater value
    By green369 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-11-2015, 02:46 AM
  4. [SOLVED] Return the min value greater than 0
    By JO505 in forum Excel General
    Replies: 6
    Last Post: 09-18-2014, 07:59 PM
  5. Fin the first value greater than and return another cell value
    By va3gjg in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-25-2013, 09:49 PM
  6. [SOLVED] Return next value in a column greater than zero
    By sordid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-05-2012, 09:29 AM
  7. Return the Greater Value
    By Ocean Zhang in forum Excel General
    Replies: 1
    Last Post: 08-27-2011, 03:19 PM

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