I usually find it convenient to use "" in formulas such as the following:

=IF(B120,B12/C4,"")

The problem of course is that an error will be returned if the cell left
blank with "" is used in subsequent calculations. Is there an alternative to
using zero instead of "" that still makes it obvious that the cell is
intentionally left unpopulated but that does not result in #VALUE# errors?

KG wrote:
I usually find it convenient to use "" in formulas such as the following:

=IF(B120,B12/C4,"")

The problem of course is that an error will be returned if the cell left
blank with "" is used in subsequent calculations. Is there an alternative to
using zero instead of "" that still makes it obvious that the cell is
intentionally left unpopulated but that does not result in #VALUE# errors?

------------------------

That's what the function NA() is designed to do. When any cell
references it, that cell will also display #NA meaning that a value is
not available.

Bill

KG Wrote:
I usually find it convenient to use "" in formulas such as the
following:

=IF(B120,B12/C4,"")

The problem of course is that an error will be returned if the cell
left
blank with "" is used in subsequent calculations. Is there an
alternative to
using zero instead of "" that still makes it obvious that the cell is
intentionally left unpopulated but that does not result in #VALUE#
errors?
Hi,
You could use a - 0 - instead of - "" -
=IF(B120,B12/C4,0)
then use Conditional Formating, for when the cell has a - 0 - the font
would be white.
That way you would not see it and your other calculations would be ok.
Thx
Dave


--
Piranha
------------------------------------------------------------------------
Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435
View this thread: http://www.excelforum.com/showthread...hreadid=374747

And it could be simpler to adjust the subsequent formula:

=e1+e2+e3+e4

could be changed to
=sum(e1:e4)
or
=N(E1)+N(E2)+N(E3)+N(E4)

And if it was ok to hide all the 0's--even those that return 0 in the formula,
you could a custom format of something like:
General;-General;;

As well as the Format|Conditional formatting that Piranha suggested.



KG wrote:

I usually find it convenient to use "" in formulas such as the following:

=IF(B120,B12/C4,"")

The problem of course is that an error will be returned if the cell left
blank with "" is used in subsequent calculations. Is there an alternative to
using zero instead of "" that still makes it obvious that the cell is
intentionally left unpopulated but that does not result in #VALUE# errors?

--

Dave Peterson

I usually find it convenient to use "" in formulas such as the
following:

=IF(B120,B12/C4,"")

The problem of course is that an error will be returned if the cell
left blank with "" is used in subsequent calculations. Is there an
alternative to using zero instead of "" that still makes it obvious
that the cell is intentionally left unpopulated but that does not
result in #VALUE# errors?

A key question is, "how is the subsequent calculation supposed to treat the
cell?" If you want the cell to be treated as zero, my advice is to have the
cell's formula evaluate to zero. If you don't want zero displayed there,
then use either custom formatting or conditional formatting or
Tools Options View (uncheck) Zero values
to make Excel display what you want.