Assessing Blank Cells That Are Not Actually Blank

[Amosbroker]

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.

 

[dreid1011]

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.

 

[Amosbroker]

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]

You could also test the length of C2, and you could shorten the formula slightly by combining the 2 AND functions into an OR:

=IF(LEN(C2),IF(OR(AND(B2="Male",C2>40),AND(B2="Female"),C2>35),"High","Healthy"),"Unknown")