Seeking an alternative to blank ("") cell in IF arguments

[KG]

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

=IF(B12>0,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?

[Bill Martin -- (Remove NOSPAM from address)]

KG wrote:
> I usually find it convenient to use "" in formulas such as the following:
>
> =IF(B12>0,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

[Piranha]

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

=IF(B12>0,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(B12>0,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

[Dave Peterson]

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(B12>0,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

[Jay]

> I usually find it convenient to use "" in formulas such as the
> following:
>
> =IF(B12>0,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.