Blanks and Zeros

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

I have the following code

=IF(Main!H5=0," ",IF(Main!H5>12,1,IF(Main!H5>=10,2,IF(Main!
H5>=8,3,IF(Main!H5>=5,4,5)))))

basically if the cell is blank I want nothing on the
following spreadsheet However if the cell value is 0 I
need 5 not a blank. I have tried =If (main!h5=" ".....but
get the same. How can I get a difference between a zero
and a blank?
 
" " is a space, . . .
Correct.

. . . "" is a blank.
Click to expand...

Wrong. "" is a zero-length string. If ISBLANK(x) is True, then COUNTA(x) is
zero. ISBLANK("") returns False, and COUNTA("") returns one.

Given your exact specs, use

=LOOKUP(IF(ISNUMBER(Main!H5),Main!H5,Main!H5=""),
{-1E+300;5;8;10;12.0000000000001;FALSE;TRUE},{5;4;3;2;1;"Invalid!";""})

which does treat Main!H5 truly blank and "" the same, returning "". If only true
blanks should return "", use

=LOOKUP(IF(ISNUMBER(Main!H5),Main!H5,ISBLANK(Main!H5)),
{-1E+300;5;8;10;12.0000000000001;FALSE;TRUE},{5;4;3;2;1;"Invalid!";""})
 
Back
Top