Assessing Blank Cells That Are Not Actually Blank

Amosbroker

New Member
Joined
Mar 26, 2018
Messages
31
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.
 

Some videos you may like

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
Joined
Jun 4, 2015
Messages
2,129
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
Joined
Mar 26, 2018
Messages
31
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
Joined
Jun 17, 2005
Messages
7,764
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")
 

Watch MrExcel Video

Forum statistics

Threads
1,108,973
Messages
5,525,987
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top