Unanswered: Why does #VALUE! show when adding blank cells?
Does anyone know why #VALUE! is displayed in the formula "=A1+B1" when one of the cells is blank, or is it known among experienced Excel users not to use this kind of addition where there is the possibility of having blank cells? The spreadsheet is from columns of numbers imported from a text file and I formatted the columns as "number". The length of a "value" in a blank cell is zero, so there is no text such as a blank space.
I typed a zero in the blank cell and the addition worked. I deleted the zero and the addition continued to work but the cell format did not change--still "number". What changed to make it work this time with a blank cell?
I always use "=SUM(..." but am curious about this behaviour with cell addition in Excel 2003 (SP1).
The key is "imported from a text file". You will get this result because Excel does not consider those as numbers but as text. If you need to convert them, go to a blank cell to the right of the table and type in the number 1. Then copy that cell, then select the column(s) that you want as numbers, and Paste Special > Multiply. Click OK. Excel then converts them all to numbers.
old, slow, and confused
but at least I'm inconsistent!