Date: prev next · Thread: first prev next last
2011 Archives by date, by thread · List index

Hello Rainer,

Rainer Bielefeld schrieb:

The problem is caused by the reasons for
"Bug 37860 - Formula returns #VALUE in 3.4RC2 but works as expected in 3.3"
Does anybody know a simple solution how to leave calculated cell
contents "" (Empty) instead of Number Value "0" in column H of attached

I have just a look in the spec. The needed implicit conversion is decsribed in chapter 6.3.5 Conversion to Number,
Text: The specific conversion is implementation-defined; an evaluator may return 0, an Error value, or the results of its attempt to convert the Text value to a Number (and fall back to 0 or Error if it fails to do so). Evaluators may apply VALUE() or some other function to do this conversion, should they choose to do so. Conversion depends on the actual locale the application runs in, especially if group or decimal separators are involved. Reference: If the reference covers more than one cell, do an implied intersection to determine which cell to use. Then obtain the value of the single cell and perform the rules as above. If the calculation setting “precision-as-shown” is true, then convert the number to the closest possible representation of the displayed number. If the cell is empty (blank), use 0 (zero) as the value. Evaluators may choose to convert references to Text in a different manner than they handle converting embedded Text to a Number.

So LO may use a zero for calculating with an empty string. I personally would support this, because this will simplify the formulas much.

Workaround exist some:
(1) Write 0, and format the cell to not show the 0. Use the format code Standard;Standard;"" for example. (2) Use a function with range, for example sum(H2:H2) instead of a simple reference H2.
(3) Use a case distinction IF(H2="";0;H2) instead of simple reference H2.
You can hide (2) and (3), by defining a name for the expression.
(4) In OOo you can use N(H2) instead of H2, not in LO.

The problem is connected to function N and should be solved together with N in the source code.

We should try to get a consensus how LO calculates with text. How are such decisions done? For OOo Oracle had decided, but here?

Kind regards

Unsubscribe instructions: E-mail to
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.