+ Reply to Thread
Results 1 to 8 of 8

IF cell is between % AND % return text + IF another...

  1. #1
    Forum Contributor
    Join Date
    05-29-2017
    Location
    UK
    MS-Off Ver
    Pro Plus 2016
    Posts
    301

    IF cell is between % AND % return text + IF another...

    Hi guys,

    I got this formula which should be simple, but for some reason can't make it work.

    Original one is:

    =(IF(AND(AA5>10%,AA5<19.999%),"C")

    and it returns result C, so all good here.

    But trying to blend few criterias, and nothing happens, I get ERROR.

    Basically, the goal is: IF values are between IF below 10% show D, IF between 10%-19.99%, show C, IF between 20%-29.99% show B, IF >= 30% show A

    How do I blend these together, please?

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,534

    Re: IF cell is between % AND % return text + IF another...

    Your formula isn't telling it what to do if the condition is outside of those values. it should be something like =IF(AND(AA5>10%,AA5<19.999%),"C", something else)
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

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

    Re: IF cell is between % AND % return text + IF another...

    =vlookup(c1,$a$1:$b$7,2)
    Attached Files Attached Files

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,534

    Re: IF cell is between % AND % return text + IF another...

    Here is an alternative because I only addressed the first part of your question but not the second part...
    =LOOKUP(AA5,{0,0.1,0.2,0.3},{"D","C","B","A"})

  5. #5
    Forum Contributor
    Join Date
    05-29-2017
    Location
    UK
    MS-Off Ver
    Pro Plus 2016
    Posts
    301

    Re: IF cell is between % AND % return text + IF another...

    Thanks for your help guys. But it isn't lookup function we looking for here, sorry if made it unclear somewhere.

    It really is just:

    =(IF(AND(AA5>10%,AA5<19.999%),"C") (which works well on it's own, but a problem starts when blending in additional criterias)
    +
    (IF(AND(AA5>20%,AA5<29.999%),"B")
    +
    (IF(AA5>30%,"A")
    +
    (IF(AA5<10%,"D")

    Is there a way to mix these criterias together? I tried

    =(IF(AND(AA5>10%,AA5<19.999%),"C")+ (IF(AND(AA5>20%,AA5<29.999%),"B")

    or

    =(IF(AND(AA5>10%,AA5<19.999%),"C"),(IF(AND(AA5>20%,AA5<29.999%),"B")

    but it keeps returning ERROR or VALUE results :-(

  6. #6
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    Re: IF cell is between % AND % return text + IF another...

    You want nested IF statements:
    Please Login or Register  to view this content.
    ...which returns the same as the LOOKUP formula in post#4

    The tests are done sequentially, so testing for higher values is only done if lower values were not found. This eliminates the need for AND statements with two criteria each.
    Last edited by leelnich; 09-01-2017 at 02:22 PM.
    Clicking the Add Reputation star below any helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion. (Thread Tools above Post # 1) Thanks!-Lee

  7. #7
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,534

    Re: IF cell is between % AND % return text + IF another...

    well this would be the "consolidation" of those formulas, I would reorder it to this...
    =IF(A1155>=30%,"A",IF(AND(AA5>20%,AA5<29.999%),"B",IF(AND(AA5>10%,AA5<19.999%),"C",IF(AA5<10%,"D"))))
    but it will produce D as a result even if the cell is blank. You might want to proceed the formula with IF(AA5="","" then follow it with the rest.
    and my lookup formula is easier and shortens the IF / then formula.

    EDIT: oh, and leelnich's formula is also cleaner and simpler than yours too.

    EDIT2: goes to show you what just trying to answer the question does, I should've known to give you the simpler version that leelnich gave you in post #6 as I know that you don't need the AND statements in between. Sheesh! Good one leelnich!
    Last edited by Sam Capricci; 09-01-2017 at 02:20 PM.

  8. #8
    Forum Contributor
    Join Date
    05-29-2017
    Location
    UK
    MS-Off Ver
    Pro Plus 2016
    Posts
    301

    Re: IF cell is between % AND % return text + IF another...

    =IF(AA5<10%,"D",IF(AA5<20%,"C",IF(AA5<30%,"B","A")))

    That was it! Thank you guys, appreciate your help. You're absolutely awesome :-)

    Have a great weekend.

+ 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. [SOLVED] How to match a cell with text with a range of text and return own text
    By ec4excel in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-16-2014, 11:07 AM
  2. [SOLVED] Compare text in a cell to text in a column on another sheet & return text from 3rd cell
    By marks@bcmr.com in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-06-2014, 05:33 AM
  3. Formula to find and return text string within a cell full of text strings
    By LightingPop in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-03-2013, 11:45 PM
  4. Search cell for multiple text , return comma separated text in separate cell if found
    By dangerdoug in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-01-2013, 01:52 PM
  5. If cell contains text1, text2 or text 3 return this text, otherwise return X
    By bukmanodrama in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-26-2013, 09:24 AM
  6. Replies: 6
    Last Post: 02-28-2013, 11:27 AM
  7. Replies: 7
    Last Post: 01-24-2013, 06:55 PM

Tags for this Thread

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