Download it now!
Bug 65182 - sum(foo)×0,95 ≠ sum(foo×0,95)
Summary: sum(foo)×0,95 ≠ sum(foo×0,95)
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.0.2.2 release
Hardware: Other All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2013-05-30 15:37 UTC by Nicolas Mailhot
Modified: 2015-03-08 08:34 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Testcase (10.63 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-05-30 15:37 UTC, Nicolas Mailhot
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Nicolas Mailhot 2013-05-30 15:37:34 UTC
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)
Comment 1 Jorendc 2013-05-30 15:47:35 UTC
In column A there are cells with a quote-mark (') in front of them. If you delete them, the result is correct.
Comment 2 Jorendc 2013-05-30 16:00:08 UTC
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
Comment 3 Nicolas Mailhot 2013-05-30 18:29:21 UTC
That is not the point of the bug

sum  will ignore the cell, but multiply won't

I'm asking for consistent behaviour here
Comment 4 Jorendc 2013-05-30 18:38:47 UTC
a moment, I'll fiddle with the bug status to mark it again as UNCONFIRMED. Someone else'd take a look.
Comment 5 ign_christian 2013-05-31 10:22:05 UTC
> 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)
Comment 6 Jorendc 2013-05-31 11:21:02 UTC
(In reply to comment #5)

Thanks ! 
Lets mark it as such then :-). NEW.
Comment 7 QA Administrators 2015-03-04 02:18:44 UTC
** 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
Comment 8 Jean-Baptiste Faure 2015-03-08 08:34:43 UTC
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