Created attachment 80046 [details] Testcase In the attached test case, the result of summing and multiplying cell values differs depending on the order with which the operators are applied This is totally un-intuitive and error-inducing, calc should never let sum(foo)×0,95 ≠ sum(foo×0,95)
In column A there are cells with a quote-mark (') in front of them. If you delete them, the result is correct.
Following some documentation I found, a single-quote in front of a cell value is a 'force-text' mark. These cells are handled as text. So the SUM() function just skip those cells, and doesn't count them. Remove the single-quotes, result in good behavior. RESOLVED NOTABUG Kind regards, Joren
That is not the point of the bug sum will ignore the cell, but multiply won't I'm asking for consistent behaviour here
a moment, I'll fiddle with the bug status to mark it again as UNCONFIRMED. Someone else'd take a look.
> Following some documentation I found, a single-quote in front of a cell > value is a 'force-text' mark. These cells are handled as text. So the SUM() > function just skip those cells, and doesn't count them. Remove the > single-quotes, result in good behavior. I agree, single-quote followed by value should be treated as text. But cell B1,B2,B5,etc treat cell A1,A2,A5 as value. Doing short testing, found that behavior not only applies to multiplication but also applies to addition, subtraction, division. SUM function goes correctly by ignoring cell with single-quote. I think it's a bug. Tested on LO 4.0.4.1 (Win7 32bit)
(In reply to comment #5) Thanks ! Lets mark it as such then :-). NEW.
** 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 (4.4.1.2 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 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) Thank you for your help! -- The LibreOffice QA Team This NEW Message was generated on: 2015-03-03
The inconsistency has been solved in LibreOffice 4.3. Indeed you can now choose how to handle text representing numbers in formulas. If you choose treat as zero, both formulas give the same result. To choose how text is converted to number: menu Tools > Options > LibreOffice Calc > Formula > Detailed calculation settings > Custom Closing as WorksForMe. If you think that SUM() should comply with this setting, please file a new bug report. If you think that the documentation of the SUM function should tell that it does not comply with this setting, please file a bug report proposing a short text to be added to the help. Best regards. JBF