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. This *is* a bug, since the correct behaviour would be to signal an error indicating the formula has been applied to invalid inputs. 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... --- 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) This may be related to https://bugs.freedesktop.org/show_bug.cgi?id=59309
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.
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present on a currently supported version of LibreOffice (5.0.4 or later) https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to "inherited from OOo"; 4b. If the bug was not present in 3.3 - add "regression" to keyword Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for your help! -- The LibreOffice QA Team This NEW Message was generated on: 2015-12-20
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present on a currently supported version of LibreOffice (5.1.6 or 5.2.3 https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to "inherited from OOo"; 4b. If the bug was not present in 3.3 - add "regression" to keyword Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug-20170103
I can confirm this bug, as originally reported, is in the current version of libreoffice/calc in debian testing: Version: 5.2.4.1 Build ID: 1:5.2.4~rc1-1 CPU Threads: 4; OS Version: Linux 4.8; Locale: en-US (en_US.UTF-8); The patch proposed in comment 9 does not affect the behavior (should a separate bug report be filed?)
(In reply to Leo from comment #13) > The patch proposed in comment 9 does not affect the behavior (should a > separate bug report be filed?) I don't think that is necessary. It seems taht only SUM is not affected by the option mentioned in comment #9. Functions like RAWSUBTRACT and AVERAGE do convert the string to a correct number. I will see if I can find out what's wrong with SUM and what can be done about this.
This is not a bug. There's a great deal of confusion here. A lot of functions take parameters of type NumberSequence which is defined to *ignore* all text cells in cell references, among them are SUM() and AVERAGE(), and no, also AVERAGE() does *not* convert strings to numbers, it ignores all strings as well, specifically they also don't add to the count of elements by which the sum is divided. This is how all major spreadsheet applications work. For the technically inclined, you can read that up at http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1017992_715980110 the reference case. If in that specification you take a look at SUM or AVERAGE, for example, you see those functions are defined to take one or more NumberSequence or NumberSequenceList parameters (NumberSequenceList is the result of a "union" of one or more range references, a list of NumberSequence). http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#SUM http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#AVERAGE The difference to operators (+,-,...) and functions that take a single scalar numeric value is that for those when encountering text strings it depends on the configuration how to treat conversion from text to number, under Tools -> Options -> Calc -> Formula, Detailed Calculation Settings, go to Custom Details to see available choices. The default for new installations is still "Convert also locale dependent" because that is what Excel does so when loading such document we treat it the same. BUT, that conversion mode depends on the current locale and may deliver different results or yield an error value for different locales. The recommendation is to set that to "Generate #VALUE! error" so one can spot errors early and correct number text to actual numeric values, which will work in all spreadsheet applications whereas any automatic on-the-fly conversion from text to numbers may or may not yield the same results.
(In reply to Eike Rathke from comment #15) There is still a user-level problem with all this. And that problem needs addressing. It's unrealistic to expect all users to be advanced enough to know this beforehand; and the current situation inevitably leads to a situation that ~every advanced user would obtain this knowledge through making grave errors, sometimes fatal (it might cost job someone). As the program behaviour is correct in regards to specs and design, the only way to address this user-level problem is to do best informing user about the possible errors. First, in the descriptions of related functions: in help, in function wizard, and possibly in the auto-completion tooltips, should be clarified, that referenced cells with texts looking as numbers will *not* be converted to text, regardless of text-to-number conversion mode. But also the text values themselves should be somehow highlighted in the spreadsheet - something like Excel does; possibly by creating comment-like markers in such cells. Maybe some other way, like making visual clues when defining ranges for functions - highlighting the "wrong" values in the ranges, or turning on Value Highlighting mode when defining ranges or writing formulas...... I'd say that these enhancements should be filed as separate RFEs and tracked individually (help, wizard, tooltips, cell formatting tips ...).