# Assessing Blank Cells That Are Not Actually Blank

#### Amosbroker

##### New Member
I have a worksheet where I pull values in one column and assess them in the column next to them.

First column:
contains my unique ID that is my VLOOKUP value (looking up data from another tab)

Second column:
contains gender referred to in formula that is assessed

Third Column Formula:
 VLOOKUP(A2,sixteen,10,FALSE)

<tbody>
</tbody>

Fourth Column Formula:
 IFERROR(IF(AND(B2="Male",C2>40),"High",IF(C2="","Unknown",IF(AND(B2="Female",C2>35),"High","Healthy"))),"Unknown")

<tbody>
</tbody>

My formula works except for the blank expression. Due to the formula being in the cell, it is not actually blank but appears to be so. How do I incorporate no data = Unknown in to my formula? Right now, the blanks come back as high when assessed through my formula.

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### dreid1011

##### Well-known Member
You may have to add a third comparison to the AND() portion. C2<>""

I learned this the other day, single text characters have a number value, and the text blank "" returned by your formula is, in this case, greater than 35.

Play with it a little and you'll see how it works. Type any alpha character in a cell, and then test that cell vs 0. Ex: =A1>0 should return true for any single alpha character in A1.

Upper case A-Z is in the range of 65-90 and lower case a-z is in the range of 97-122. I'm not sure exactly what number the text blank is though.

Last edited:

#### Amosbroker

##### New Member
Thanks dreid! I ended up just adding another comparison to the formula so that everything that wasn't clearly defined would fall in to the unknown category. For my greater than part of statement, I also added a less than 8000. I just used a random high number.

#### njimack

##### Well-known Member
You could also test the length of C2, and you could shorten the formula slightly by combining the 2 AND functions into an OR:
Code:
``=IF(LEN(C2),IF(OR(AND(B2="Male",C2>40),AND(B2="Female"),C2>35),"High","Healthy"),"Unknown")``

Replies
3
Views
102
Replies
2
Views
43
Replies
3
Views
27
Replies
4
Views
79
Replies
5
Views
128