Simple cell reference formula returns 0 when cell is blank

[bhujanga]

I have this simple formula form in many cells:

=IF(AND($B5=1,$A6=$A5),E6,"")

When there is nothing in cell E6 (in this case), it returns a zero instead of blank. Zero is also a valid possibility for cell E6, so I can't just change all zero returns to blanks. I can get around by using this formula:

=IF(AND($B5=1,$A6=$A5),IF(E6="","",E6),""),

but that seems unnecessarily clumsy. Is there a better way?

 

[Andrzejek]

Would that help: Return Blank Cells Instead of Zeroes in Excel Formulas

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[bhujanga]

I checked out the link. It looks like they essentially do the same thing I did (Option 3), so I guess that's just what you have to do and I'll consider the matter resolved.
Thanks for your answer.

 

[Andrzejek]

There were some pointers to some Excel's settings that returns either blank or zero.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[combo]

Simpler: =IF(AND(B5=1,A6=A5,NOT(ISBLANK(E5))),E6,"")
Blank is not empty, are zero length string (="") or space valid entries?


combo

 

[bhujanga]

Yes, the problem cells are those with nothing in them, but your formula works. I could also use: =IF(AND(B5=1,A6=A5,NOT(E6="")),E6,"")
Thanks.