[mdlug] Why is a blank cell != zero value in OpenOffice-Calc 3.5

[Adam Tauno Williams]

On Thu, 2012-11-15 at 17:56 -0500, R KANNAN wrote:
> On Thu, Nov 15, 2012 at 5:51 PM, Michael Mol <mikemol at gmail.com> wrote:
> > On Thu, Nov 15, 2012 at 5:44 PM, R KANNAN <rk111810 at gmail.com> wrote:
> > > OpenOffice Users,
> > > I opened an exiting (from OO 2.2)  spreadsheet in Oo-Calc 3.5 aand found
> > a
> > > lot of #VALUE in cells where I had a formula prodcing perfectly
> > acceptable
> > > numbers. Then I found that some of the cells where I had blanks were
> > > considered by Oo-3.5 to be strings and that is why all these formulas are
> > > failing.
> > > Why is a blank cell considered to have a zero numerical value. Is there a
> > > global setting which does this?
> > > Thanks for any input
> > Check the cell formatting? Perhaps that may be having an impact.
> No. I tried that but did not help. I had to go to each of the blank cells
> referred in the formulas and 'delete contents'. I probably have hundreds of
> these cells in non-contiguous columns and rows.

If delete contents worked then those cells were not "blank".  They
possibly contained white space or empty strings (which are not the same
thing as NULL / None).

This is a problem that plagues spreadsheets in general [not specific to
Excel, OOo, LO].  I do a lot of ETL stuff and spreadsheets are the most
troublesome data sources - most users are oblivious to the dynamic type
system [or even to the concept of type].  Hence so many zip code columns
where the values are integers [Ugh!] or what I saw yesterday: a column
meant to be a CSV list of values - 102,402,307.  Guess what?  That
column was the integer 102402307 displayed with column formatting!  It
had to be converted to a string and then split.  

To get consistent results you need to be very aware of data types and
NULL vs blank.

> This sheet was perfectly fine in OO2.2 :-(

Which was probably a bug.

> BTW I am using Libre Office 3.5 in Ubuntu 12.04 if it makes any difference.