empty cell versus 0 (zero)

[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

 

[Ken Wright]

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 .......

 

[Norman Harker]

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.

 

[Horatio J. Bilge]

Thank you, Ken and Norman. Your advice worked perfectly for me.
I appreciate your assistance.

~ Horatio J. Bilge