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

1. ## 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. ## Re: Blank cells in an "IF" & "AND" formula.

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

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

Originally Posted by Fluff13
=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. ## 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%

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

#### 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