+ Reply to Thread
Results 1 to 4 of 4

Blank cells in an "IF" & "AND" formula.

  1. #1
    Registered User
    Join Date
    01-27-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    93

    Blank cells in an "IF" & "AND" formula.

    Hi,

    I have the following formula to find values between certain criteria.

    When the formula looks in the cell which contains the text 'No Data' it gives 'A' as the answer. The cell in question already has a formula in which returns either a % value or 'No Data' based on what the values are in another cell.

    Original formula with 'No Data' in the cell but this returns 'A':

    =IF(I5>15%,"A",IF(AND(I5>8%,I5<=15%),"B",IF(AND(I5>4%,I5<=8%),"C",IF(AND(I5>0%,I5<=4%),"D",IF(I5<0%,"X",IF(I5="No Data",""))))))

    I have altered the formula to use 'ISSBLANK' but this also returns 'A'

    =IF(I5>15%,"A",IF(AND(I5>8%,I5<=15%),"B",IF(AND(I5>4%,I5<=8%),"C",IF(AND(I5>0%,I5<=4%),"D",IF(I5<0%,"X",IF(ISBLANK(I5)=TRUE,""))))))

    Any ideas?

    Terry

  2. #2
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    2013
    Posts
    1,926

    Re: Blank cells in an "IF" & "AND" formula.

    How about
    =IF(I5="No Data","",IF(I5<=0%,"X",IF(I5<=4%,"D",IF(I5<=8%,"C",IF(I5<=15%,"B","A")))))

  3. #3
    Registered User
    Join Date
    01-27-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    93

    Re: Blank cells in an "IF" & "AND" formula.

    Quote Originally Posted by Fluff13 View Post
    How about
    =IF(I5="No Data","",IF(I5<=0%,"X",IF(I5<=4%,"D",IF(I5<=8%,"C",IF(I5<=15%,"B","A")))))
    Thanks Fluff, that works fine.
    Is the order critical? I can see that you put the values in order, presumably the formula asks the question then moves on until it finds the right answer.
    What about the 'No data' at the front - any reason why that is there?
    Terry

  4. #4
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    2013
    Posts
    1,926

    Re: Blank cells in an "IF" & "AND" formula.

    With the way I've written it you can have the "No Data" either as the first or last criteria.
    But text is "Larger" than a number which is why you got an A for "No Data" as it's > 15%

+ Reply to Thread

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