Apache OpenOffice (AOO) Bugzilla – Issue 18552
Equals, setting, testing unset values
Last modified: 2013-08-07 15:15:02 UTC
An unset value tests equal to "" (nul string) and also equal to zero. A pragmatically useful feature. A cell set equal to an unset cell becomes specifically set to zero. This may be the way empty cells are painlessly used in mathematical formulae, but it is less useful because it leads to loss of information of the original value, and puts messy zeros in turn-around documents. It doesn't happen in Excel 97 It should be possible to preserve the distinction between unset values, and values specifically zero - whether the latter are input or calculated values - and explicit zero values may need to be printed. Not displaying zeros is not always an option. Fix - recode so that =A3 means what it says even when A3 is unset. Messy partial work around: For example, use =if(A3="";"";A3) and =if (A3="";0;A3) instead of =A3 The choice depends on whether the cell is a text field or part of an arithmetic process, and whether the source cell has already had the nul string work-around. (note the nul string does not test equal to zero) HELP enhancement - something about UNSET as a declartation and as a value, and how unset cells are dealt with when encountered in formulae. footnote: In other respects OpenOffice is MUCH better than Excel for what I'm doing. An explicit number format degrees-minutes-seconds would help make my formulae more transparent, but I can misuse OpenOffice hours:minutes:seconds (positive and negative)to achieve the same result. Excel 97 won't accept negative hours:minutes:seconds. Thank you for doing it better!
Hi Chris, this is a different design philosophy between Excel and OOo. So this is not a defect but an enhancement. So I set the flags and reassign it to the feature guys. Frank
It may be the intention that an unset cell is zero, but that is not the case for PRODUCT(). I think there is an inconsistency in the internal representation of cells - or in the implementation of PRODUCT(). If you enter this formula into cell A1: '=PRODUCT(B1:C1)' Cells B1 and C1 are both unset. The result is - as expected - zero. However, enter '42' into B1 and the result is '42'!! That is not the behaviour I want or expect from a function like PRODUCT(). If you set C1 equal to an unset cell result zero appears. I havn't looked at the code, but either there is an error in PRODUCT () implementaion or a flaw in the cell implementation for un- initialised cells.
First of all: Calc behaves identical to Excel in this case. One of our main requirements is to be as compatible as possible in functionality, also known as interoperability, therefore we will not change this behavior, and I'll close this issue. Now some details: Of course also Excel displays a 0 if a cell references an empty cell like =A1. I don't know how you encountered the opposite by other means than unchecking the "display zero values" option. Regarding your PRODUCT() example, even that behaves identical to Excel. And you simply don't want empty cells in a range to be calculated as zero in this case. Expand your example to the AVERAGE() function, would you like to have AVERAGE() calculate empty cells as zero? I don't think so. And neither does the competetive product.
closing
Created attachment 73509