empty cell versus 0 (zero)

  • Thread starter Thread starter Horatio J. Bilge
  • Start date Start date
H

Horatio J. Bilge

Is there a way to differentiate between an empty cell and an entered value
of zero (0)?
I want to write a formula that treats an empty cell differently from a cell
in which I have entered a zero. For instance, if one of the cells in a range
is empty, the formula returns the value "Incomplete." But if all of the
cells in the range have data entered (even if the data entered is a zero),
the formula returns the sum.

My workaround is this: I've entered 1000 in all cells within the range (1000
is an order of magnitude greater than the highest possible sum). I used
conditional formatting to make the 1000 disappear (If cell value is equal to
1000, then format font white). Then I used the formula:
=IF(SUM(A1:A10)>=1000, "Incomplete", SUM(A1:A10))
This could become problematic, because a user may enter data, then delete
it, but the formula would still calculate the sum.

Horatio J. Bilge
 
How about COUNTBLANK()

This will count really blank cells in your data, so you could use IF with it and
IF(COUNTBLANK(Rng) > 0, then .......
 
Hi Horatio!

I'll stick to the principles and you should be able to apply them following
Ken's guidance.

=ISBLANK(A1)
Returns TRUE if the cell is truly empty but returns FALSE if the cell
contains a formula that returns "". In truth the cell only *appears* blank.

=IF(A1=0,TRUE)
Returns TRUE if A1 is 0, or if A1 is truly empty. But it returns FALSE if A1
is a formula that returns ""

=IF(A1="",TRUE)
Returns TRUE if A1 is either empty or contains a formula that returns "".

=COUNTBLANK(A1)
Returns 1 if A1 is empty or contains a formula that returns "".
But note here the illogicality of this compared with ISBLANK(A1).
ISBLANK(A1) returns FALSE if A1 contains a formula returning "". But this
cell will be counted by COUNTBLANK.

=COUNTA(A1)
Returns 1 if A1 contains a formula that returns "". Returns 0 if A1 is
empty.

Which formula approach you need, depends upon what you are doing.

--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Thank you, Ken and Norman. Your advice worked perfectly for me.
I appreciate your assistance.

~ Horatio J. Bilge
 
Back
Top