Avoiding #VALUE errors on blank cells

Is it possible to inset a bank into a cell, and use that cell in a subsequent addition formula?

For example;

A1 contains 1
B1 contains 2
C1 contains =IF(A1=1,"",3)
D1 contains =A1+B1+C1

In the above case the cell D1 contains #VALUE and not 3, whereas if C1 were actually blank D1 would contain 3.

The reason I ask is that a blank is not the same value mathematically as 0. The latter is the number zero, whereas the former is the lack of any number.

Thanks

Andrew

You could use SUM(A1:C1). That works. You could also use IF(A1=1,0,3) and format the cell to not show 0's.

ChemistB

SUM only works where the cells are contiguous and the =0, formatted to blank, method would not differentiate between a zero value and a blank.

Thanks anyway

Andrew

You can use SUM with non-contiguous cells, e.g.

=SUM(A1,C1,Z1)

Hi Andrew,

I didn't mean to format the zero as a blank but to use custom numbering formatting to not display zeros. The zero will be there but not show.

ChemistB

daddylonglegs:

Good grief, it works, even Sum(A1,-B1,C1) is equivalent to =+A1-B1+C1 and it gives the right answer. Thanks, this has been a problem to me for literally years!

ChemistB:

As I tried to explain before, there is a difference between zero and blank, so formatting zeroes as blanks does not really solve the problem

Thanks to everyone

Andrew

Hi fd,

As I explained before, I'm not talking about formatting the zero's as blanks, just telling Excel not to show them.

Here's an example just to let you know I'm not crazy.

ChemistB

Yes I can see what you are saying, but looking at your example, C1 should read "0" but reads blank due to the formatting. So is C1 really zero, or do you not know what it should read? This is the difference between "0" and blank.

Andrew

In your original post, you had posted formulas that indicated that you wanted C1 to be a blank but the problem with that was your addition formula could not handle that. With my formula, anyone viewing the sheet would think that cell was blank but your formula will still work. It is true that if you're going to be performing other calculations on that cell (e.g. average or standard deviation) then my solution would not work for you.

ChemistB

Quote:

"The reason I ask is that a blank is not the same value mathematically as 0. The latter is the number zero, whereas the former is the lack of any number."

The solution posted by daddylonglegs works well as far as I can see.

Thanks for your help

Andrew

Hey fd,
try conditional formatting so that if the cell contains a zero the format changes so the font is white, thus hiding the zero.