Formula to test if a cell is equal to "0" and not blank

[DHeshMan]

I have a challenge to come up with the simplest way to test if a cell is equal to 0.

If I use A1=0, I get a TRUE response if the cell is 0 or if it is blank. If I use A1="0", I get a FALSE when it should be TRUE. I hoping to find something simpler than having to use a compound statement like AND(A1=0, NOT(ISBLANK(A1)))

Thanks

 

[bbott]

Welcome to the board.

Your first solution is the shortest. When you enter quotes around a number, the formula is looking for a text value. That's why it evaluates to FALSE.

 

[Jonmo1]

You have to do the AND, because IF (and many other Excel functions) treat blanks as 0's..

I'd suggest
=AND(A1=0,ISNUMBER(A1))

 

[bbott]

You have to do the AND, because IF (and many other Excel functions) treat blanks as 0's..

I'd suggest
=AND(A1=0,ISNUMBER(A1))

Sorry, misread the question. didn't realize you wanted to differentiate between blank and 0....

 

[Jonmo1]

You could do this instead of the AND

=A1&""="0"

 

[AkaTrouble]

i would go opposite way

IF(a1="","empty cell",IF(A1=0,"Zero Cell","Cell > Zero"))

 

[DHeshMan]

You could do this instead of the AND

=A1&""="0"

This is what i was trying to do. Looks like this is the simplest route but to avoid confusion later i will add some explicit order of operations. =(""&A1)="0"

I had tried using the command TEXT(A1,"#") to convert the number to a string, but that resulted in a blank string when A1 was 0. ""&A1 treats it like a string inherently. Thanks.