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

K

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?
 
G

Guest

KG said:
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?
Click to expand...

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

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
 
P

Piranha

KG said:
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?
Click to expand...
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
 
D

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

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?
Click to expand...

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