Re: [tdf-discuss] Simple solution to avoid #VALUE required

Hello Rainer,

Rainer Bielefeld schrieb:
> Hello,
>
> The problem is caused by the reasons for
> "Bug 37860 - Formula returns #VALUE in 3.4RC2 but works as expected in 3.3"
> <https://bugs.freedesktop.org/show_bug.cgi?id=37860>
> Does anybody know a simple solution how to leave calculated cell
> contents "" (Empty) instead of Number Value "0" in column H of attached
> "AnOtherSample.ods"?

I have just a look in the spec. The needed implicit conversion is 
decsribed in chapter 6.3.5 Conversion to Number,
<quote>
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.
</quote>

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
Regina

--
Unsubscribe instructions: E-mail to discuss+help@documentfoundation.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.documentfoundation.org/www/discuss/
All messages sent to this list will be publicly archived and cannot be deleted