How to leave a cell blank rather than "0"

[Guest]

In my worksheet I'm trying to enter a value in one column only if a value
exists in another. So, in the "IF" arguement, It would be something like:

If(G5=A1+7,49.99,IF(G5=A1+14,59.99,"0"))

The 3rd part of the arguement makes the entry 0 appear. I don't want
anything to appear, but I don't know how to make it so that the cell is left
blank if neither argument is true. I tried "" but that is invalid.

Thanks for the help.

 

[Max]

If(G5=A1+7,49.99,IF(G5=A1+14,59.99,"0"))

... I tried "" but that is invalid.

hmm .. replacing "0" with the blank: "" seems to work ok,
when I put in say,
H5: =IF(G5=A1+7,49.99,IF(G5=A1+14,59.99,""))

 

[Guest]

Hi,

The "" should work.

I just copied your formula and added the "" and it worked fine.

=IF(G5=A1+7,49.99,IF(G5=A1+14,59.99,""))

 

[Alan]

In my worksheet I'm trying to enter a value in one column only if a
value exists in another. So, in the "IF" arguement, It would be
something like:

If(G5=A1+7,49.99,IF(G5=A1+14,59.99,"0"))

The 3rd part of the arguement makes the entry 0 appear. I don't
want anything to appear, but I don't know how to make it so that the
cell is left blank if neither argument is true. I tried "" but that
is invalid.

Thanks for the help.

Hi,

I have had the same problem myself over many years.

The use of "" returns an empty string - not the same as nothing which
is, it seems, what you actually want.

I have never found a way to do that. If a cell contains a formula,
then it evaluates to *something* which is always logically different
to the *nothing* in an empty cell. I have never figured out why excel
lacks a simple NULL() formula that evaluates to *nothing*.

However, it doesn't so you have to find an alternative route.

Depending on your specific situation and objective, often the best
workaround I have found is to return something like NA().

When you reference that cell, you can check for NA() by using
ISNA(Cell) and trap for that occurance and then treat it as you do for
*nothing*. However, what will or won't work for you depends on what
you are trying to achieve.

HTH,

Alan.