+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Forum Contributor
    Join Date
    01-27-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    120

    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 Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    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
    Forum Contributor
    Join Date
    01-27-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    120

    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 Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    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)

Similar Threads

  1. Replies: 5
    Last Post: 02-05-2019, 12:03 AM
  2. Replies: 0
    Last Post: 02-14-2018, 06:14 PM
  3. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  4. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  5. Replies: 3
    Last Post: 02-16-2011, 02:55 PM
  6. Replies: 5
    Last Post: 10-12-2010, 06:46 AM
  7. Replies: 3
    Last Post: 12-14-2006, 01:36 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