Excel: why the value of a blank cell is zero?

Question

Suppose there is an empty excel sheet. Enter formula "=A1" into the B1 cell. The value of B1 would be 0.

My question is: why the value of B1 becomes zero when referring to an empty cell A1? Any rationales Excel behaves this way?

Thanks.

Olives

Although not answering the question directly, worth taking a look at:#1120114 and #32170458 – Acrocarpous

In short, Excel is assuming that you want a number as an output and thus coerces the value from the original cell (which is nothing, so 0), and gives 0 as an output. – Lubeck

You can still display blank using "format cell" as explained in the second answer in my first comment. It seems logical for a spreadsheet program to assign a default value of zero to all cells and let user manipulate the presentation later. – Acrocarpous

@Acrocarpous yes, i read the two posts you mentioned before asking the question. Since I am from other programming language camp, it strikes when excel does this. To me, the most logical solution is that the value of blank is just blank. Well, just my personal choice. I can take the 0 value anyhow. – Olives

FWIW, force a zero-length string with =A1&"". This can also be used to show (an apparently blank) cell when a VLOOKUP of INDEX/MATCH wants to return a zero after encountering a blank cell to return. Two caveats: first, a zero-length string is not truly blank and second, if used with VLOOKUP(...)&"" then any true number that should have been returned as a true number becomes text-that-looks-like-a-number. – Carat

Answer 1

That is because the formula in B1 returns the value of cell A1.

Excel assigns the value 0 to a blank cell.

To distinguish 0 from blank, in B1 enter:

=IF(A1="","",A1)

Debtor

let's consider A1 is empty. B2 is =A1&"foo". it results to "foo" and not to "0foo". why? – Selfopinionated

Answer 2

FWIW, force a zero-length string with =A1&"". This can also be used to show (an apparently blank) cell when a VLOOKUP of INDEX/MATCH wants to return a zero after encountering a blank cell to return. Two caveats: first, a zero-length string is not truly blank and second, if used with VLOOKUP(...)&"" then any true number that should have been returned as a true number becomes text-that-looks-like-a-number. – Jeeped

Quoting the best answer so I can vote on it :)

I changed my application to =formula&"" according to Jeeped, and works great. Kinda dumb that Index returns Value(formula).

Lydalyddite

Answer 3

Simplest way I have found is when I am copying data across to a table, if at anypoint I am entering an empty string I will put a space in it so " " instead of "". if thee is a space in the cell you are creating a function from it will return a blank cell rather than a 0. especially when I have very long if functions I don't want to be adding a further if(isblank()) to it all.

Drusy