Hi Leo,

(In reply to comment #0)
> In calc, a formula like sum(a1:a3) applied to cells which contain a string
> results in the string being coerced to 0. However, if the the string
> contains a number, this coercion is incorrect.

Under you point of view.
This works as intended and I hope this will never change.

> 
> This *is* a bug, since the correct behaviour would be to signal an error
> indicating the formula has been applied to invalid inputs.

An error result only leads to make unusable the SUM() function for many people like me. 

> 
> This bug bit when copying numbers from one program into libreoffice/calc.
> One of the numbers was read as a string by calc, and the resulting column
> tally was off by ~$400. This wasn't noticed til it was too late...

you have options to make the import in number properly, e.g. with paste special - unformatted text, you can select the right data type.

> 
> ---
> Btw, I discovered after the fact that View->Value Highlighting provides a
> way to diagnose problems with string->0 coercion.
> 
> ---
> Here is a simple example:
> 
> a1: "I am not a number"
> a2: =sum(a1:a1)

Result 0, what it is right, nothing to sum.

> 
> This may be related to
> https://bugs.freedesktop.org/show_bug.cgi?id=59309

I think it is your responsibility have the data in the proper way, numbers are numbers not text.
If you want you can set easily verifications to be sure no text data in the list.

(In reply to comment #1)
> Hi Leo,
> 
> (In reply to comment #0)
> > In calc, a formula like sum(a1:a3) applied to cells which contain a string
> > results in the string being coerced to 0. However, if the the string
> > contains a number, this coercion is incorrect.
> 
> Under you point of view.
> This works as intended and I hope this will never change.

We have opposing desires.

> 
> > 
> > This *is* a bug, since the correct behaviour would be to signal an error
> > indicating the formula has been applied to invalid inputs.
> 
> An error result only leads to make unusable the SUM() function for many
> people like me. 

Look, you are completely missing why this is a bug.

A user copies a "number" like 190.20 from thunderbird or wherever into calc. Calc sees this number as a string and coerces this "number" to 0 in sum, et. al. No warnings, no errors, nothing. That is absolute crap. And how is an unsophisticated user even to know where or why the error happened?

> 
> > 
> > This bug bit when copying numbers from one program into libreoffice/calc.
> > One of the numbers was read as a string by calc, and the resulting column
> > tally was off by ~$400. This wasn't noticed til it was too late...
> 
> you have options to make the import in number properly, e.g. with paste
> special - unformatted text, you can select the right data type.

Let me be clear, I identified this bug because another user came to me telling me what crap libreoffice is, it can't even sum a column of numbers correctly. Who would expect that pasting a damn number into libreoffice office would require such damn contortions as you are suggesting? Come on.

> I think it is your responsibility have the data in the proper way, numbers
> are numbers not text.

Indeed, and when calc, by default, displays a stringified number as a number and *silently* coerces that string to 0, then calc violates your principal. That is the bug report I am filing.

> If you want you can set easily verifications to be sure no text data in the
> list.

So, an unsophisticated calc user can be bitten in the ass by a bug that can be easily fixed if only the user were more sophisticated. Just so the sophisticated user can take advantage of some short cuts that could be easily customised. Come on.

Please excuse the polemical tone of my last reply.

It is a bug for calc to, as a default, display strings consisting of numbers as numbers. And, it is a bug for calc to, as a default, coerce a string that consists of numbers to 0 without signaling a warning or error.

We need to get another voice in here from QA - moving to UNCONFIRMED. If a second person confirms that the reported desire isn't wanted, we should close this as WONTFIX. Else it should be moved to NEW. Thanks all.

Calc is inconsistent in conversion text to numbers. When I format A1 as text and write "1", B1 = A1+1, result is 2. When I write formula =SUM(A1), result is 0. In first case automatic conversion text to numbers works, in second case text is not converted to number.

Version: 4.4.0.0.alpha2+
Build ID: 98f9baa5253f7bb8034f148519f31f548b1452fa
TinderBox: Linux-rpm_deb-x86_64@46-TDF, Branch:master, Time: 2014-11-12_00:13:54

Calc do it the same way as excel.

(In reply to raal from comment #5)
> Calc is inconsistent in conversion text to numbers. When I format A1 as text
> and write "1", B1 = A1+1, result is 2. When I write formula =SUM(A1), result
> is 0.

CONFIRMED in LO 4.4.0.0.alpha2 + Ubuntu 14.04

To sum up (no pun intended), operators such as "+, -, /, *" coerce numbers formatted as text to their apparent value, whereas functions such as SUM() and AVERAGE() convert all strings to a value of zero.

> In first case automatic conversion text to numbers works, in second
> case text is not converted to number.

In both cases the text is converted to a number, it's just that the built-in functions map all text inputs to zero :P

> Calc do it the same way as excel.

Hmm...that's a good data point, even if I think that the behavior is suboptimal. At the very least, we need to document this nuance of Calc. What version of Excel are you using to test?

Status -> NEW

(In reply to Robinson Tryon (qubit) from comment #6)
> (In reply to raal from comment #5)
 What
> version of Excel are you using to test?

Excel 2010

(In reply to Robinson Tryon (qubit) from comment #6)
> (In reply to raal from comment #5)
> > Calc is inconsistent in conversion text to numbers. When I format A1 as text
> > and write "1", B1 = A1+1, result is 2. When I write formula =SUM(A1), result
> > is 0.
> 
> CONFIRMED in LO 4.4.0.0.alpha2 + Ubuntu 14.04
> 
> To sum up (no pun intended), operators such as "+, -, /, *" coerce numbers
> formatted as text to their apparent value, whereas functions such as SUM()
> and AVERAGE() convert all strings to a value of zero.
> 
> > Calc do it the same way as excel.
> 
> Hmm...that's a good data point, even if I think that the behavior is
> suboptimal. At the very least, we need to document this nuance of Calc. What
> version of Excel are you using to test?
> 
> Status -> NEW

Thanks for looking into this.

Excel's bugs shouldn't be replicated in libreoffice, imo.

a1+a2 and sum(a1:a2) should produce the same output. The latter is just an abbreviation for the former.

There is a new option in 4.3.0 and following version.
This option allow the choice on how strings must be treated in arithmetic operation.

See http://erack.org/blog/archives/40-LibreOffice-4.3-new-Calc-feature-user-selectable-text-conversion-models.html

(In reply to GerardF from comment #9)
> There is a new option in 4.3.0 and following version.
> This option allow the choice on how strings must be treated in arithmetic
> operation.
> 
> See
> http://erack.org/blog/archives/40-LibreOffice-4.3-new-Calc-feature-user-
> selectable-text-conversion-models.html

Looks interesting, but I couldn't seem to find an option in that config that would make built-in functions convert strings to (non-zero) numbers, so if A1 = 1, then SUM(A1) = 0, even though SUM(0 + A1) = 1.