Apache OpenOffice (AOO) Bugzilla – Issue 82725
Same empty cell evaluted differently when referenced directly vs through (v)lookup
Last modified: 2013-08-07 15:13:47 UTC
An empty cell is evaluated as '0' (numeric zero) when directly referenced (eg such as 'J10') but is evaluated as <something else (I do not know what but NOT a zero)> when that same cell is referenced indirectly as the result of a vlookup. Subsequent cells that depend on or use the 'zero' give one result if the cell was directly referenced but another result of the cell has been looked up. I found this issue when trying to run a financial model implemented in Excel under OOo. The behaviour of OOo is consistent with Excel when the empty cell is directly referenced, but inconsistent with Excel when referencing empty cells through a 'lookup'. I have prepared a very simple spreadsheet which shows the inconsistency. A simple data table is shown highlighted in orange. (The data table contains numbers, text, and an empty cell.) Two different references to that data are shown highlighted in green. The 'looked up' reference to an empty cell gives a blank, while the direct reference to the same empty cell gives a zero. At the bottom of the sheet a simple test checks whether two answers are consistent or inconsistent. I have run this on 2 different PCs, one under Debian Etch and OOo 2.0, the other under Windows 98 OOo 2.2, with the same result that OOo is returning different numeric values depending on the nature of the cell reference. Apologies if this report is a duplicate - I found lots of reports about 'empty cells' - mostly fixed in fact - but nothing that suggested that anyone else had reported inconsistent answers from the same cell depending on the manner of cell access. Apologies, also, if this issue has already been dealt with - I only have dialup, and cannot easily obtain either 2.3 for Windows, or a backport of OOo 2.x for Debian Etch / 4 / stable. regards, Ron (grateful for what is being achieved, wishing the team all the best)
Created attachment 48972 [details] Spreadsheet showing different evaluation of empty cell depending on access method
Hi, this is not a bug from Calc but from Excel I think. An empty cell is not Zero but empty. So your example isn't with the correct parameter. If you use "" instead of 0 the lookup works as expected. A formula must have a result. A simple reference as in your case is a formula and it's assumed that the referenced cell is nummerical the resulting cell shows a zero as lowest neutral number. IMHO this Issue should be closed invalid. Any other opinions here ? Frank
Frank, thanks for a quick response. You said "An empty cell is not Zero but empty." But the report is highlighting that the SAME cell gives rise to TWO DIFFERENT evaluations depending - Not on the cell contents Not on whether a numeric value is being requested but only on whether the cell is directly or indirectly referenced. Regardless of whether the value of a cell should or should not be '0' or 'empty' - should it's numerical value be the same as a result of either types of referencing mechanism? Or should the numerical value of a cell be different and not carry through if a cell is looked up? From the user's perspective, I think we would hope that a cell's value would be consistent however accessed. I haven't posted this in the public forum, so there may not be many (any?) users who see this report and conversation. But does the team normally value a wider user input? I am uncomfortable with publicising this issue because I feel it reveals an internal inconsistency in the application, and an incompatibility which will inhibit migration or adoption of OO - both are aspects that could be cited to discourage OO's wider acceptance. If the team does want further user input, perhaps the team could decide what to post on the public forums. regards, and thanks again for a quick response, Ron
My general opinion is that this is an inconsistency in how spreadsheet applications handle empty cells/missing values. [And more generally how data is handled.] This is both a problem of OOo and of MS Excel. [And to a smaller degree of end user who do not understand the concept of missing values and of data types.] More advanced statistical packages [like http://cran.R-project.org] have a special "NA" data type for missing values and are able to handle more consistently such situations. [R is not a spreadsheet, so the functions differ and cannot be directly compared, but it has a separate 'NA' data-type. The data-handling is built in a different way, too. E.g. conversions from a user-defined data type are not implicit, but the creator of the data-type implements *inside* the new data-type specific conversion routines. So, the *responsibility* belongs to the implementor of the new data type to make the conversions, when a different data type is requested, allowing therefore for consistency!]
Hi Niklas, please comment on this one. Frank
Additional information: Gnumeric 1.6.3 on Debian 4 is consistent in its evaluation of an empty cell; Gnumeric returns the same value regardless of whether the reference is 'direct' or 'via lookup'. (It is this inconsistency in OOo that is the issue raised in this report.) I tried and failed to check what KOffice did - KOffice uses an '@' function similar to early Lotus 123, and my first attempt at @LOOKUP failed - I think I have forgotten how to define a range in Lotus, it has been so long since I used it. My version of Corel Office (v8) does not read xls files so I have not been able to check what Corel does. Is it relevant or helpful to check whether other competitor applications handle cells consistently? - if it is, I will try to create an L123 equivalent to run through KOffice and Corel to see what they do so I can report back. Incidentally, Gnumeric evaluates empty cells to 'zero' in the same manner as Excel does. I don't really think the problem of inconsistency in OOo can be put down to a 'bug in Excel', or even to any uncertainty on the part of a user as to his intentions in processing sparse data arrays. As a user, I strongly recommend that the application handles cells consistently. And as an advocate of OOo, may I plead that the application handles empty cells in the same manner that Gnumeric and Excel do, so that those of us working with Governments and the Consultancies worldwide can be confident that existing models and spreadsheets using in-built functions will run? regards, and thanks for taking the time on this. Ron
Duplicate. *** This issue has been marked as a duplicate of 32340 ***
Closing dup.