3

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.

Gang Liang
  • 583
  • 6
  • 14
  • 1
    Although not answering the question directly, worth taking a look at:http://stackoverflow.com/questions/1119614/return-empty-cell-from-formula-in-excel and http://stackoverflow.com/questions/32169958/if-0-then-leave-the-cell-blank – jrook Apr 03 '17 at 17:59
  • 1
    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. – Brandon Barney Apr 03 '17 at 18:05
  • 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. – jrook Apr 03 '17 at 18:05
  • @jrook 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. – Gang Liang Apr 03 '17 at 19:16
  • 1
    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. –  Apr 03 '17 at 23:20

2 Answers2

4

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)
Gary's Student
  • 91,920
  • 8
  • 47
  • 75
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).

Hans Schulze
  • 86
  • 7