Download it now!
Bug 98844 - SUM function does not take into account precision as shown for ranges with numbers only (comment #7).
Summary: SUM function does not take into account precision as shown for ranges with nu...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.0 all versions
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:6.4.0 target:6.3.3 target:6.2.8
Keywords: bibisected, needUITest, regression
: 103600 (view as bug list)
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2016-03-23 15:14 UTC by Voloshin Sergej
Modified: 2019-09-17 08:56 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
Screenshot Calc (255.42 KB, image/png)
2016-03-23 15:14 UTC, Voloshin Sergej
Details
file.ods (19.01 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-03-24 16:08 UTC, Voloshin Sergej
Details
Precision as Shown (12.11 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-03-25 10:31 UTC, Tim R
Details
Precision as Shown (12.06 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-03-25 10:32 UTC, Tim R
Details
Sample showing how it works (9.67 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2016-03-25 11:48 UTC, m.a.riosv
Details
Another example with bug (10.85 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-09-06 09:46 UTC, luca.manganelli
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Voloshin Sergej 2016-03-23 15:14:47 UTC
Created attachment 123794 [details]
Screenshot Calc

How to configure so that would be considered with an accuracy of "as displayed"? 
Options> Calculation>calculate is set the check-box "Precision as displayed", but it's not working!! 
For example (pic in attach): In the cells are calculated values: 11,89 11,75 11,87 4,03 8,06 The format of each cell is specified the fractional part:2, - accuracy two digits after the decimal point. If you add the digits of the number of turns = 47,6. Calc gives 47,62. 
Indeed, if you add up all the numbers with a given precision of 3 digits after the decimal point, we get approximately 47,615.... Thus, Сalc shows some values (with two digits after the decimal point) but calculation maintains maximum precision for him. It's good, but in this case I kind of precision is not required. 
I apologize for my bad English,I'm Russian.
Comment 1 Jean-Baptiste Faure 2016-03-24 09:45:06 UTC
Please attach a sample document, as this makes it easier for us to verify the bug. I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' once the requested document is provided. 
(Please note that the attachment will be public, remove any sensitive information before attaching it. See https://wiki.documentfoundation.org/QA/FAQ#How_can_I_eliminate_confidential_data_from_a_sample_document.3F
for help on how to do so.)

Best regards. JBF
Comment 2 Voloshin Sergej 2016-03-24 16:08:40 UTC
Created attachment 123814 [details]
file.ods

Added file. When I removed the first sheet of "Общие"(General),сalculations on the second sheet turned out right. Most likely an error in the file??
Comment 3 Jean-Baptiste Faure 2016-03-25 06:02:18 UTC
Thank you for the test file.
Partially reproducible :
1/ with the master: when opening the file, everything is recalculated and I see 47,60 in T17
2/ with LO 5.1.3.0.0+: when opening the file I see 47,62 in T17, but, forcing an hard recalculation (Ctrl+Maj+F9), I get 47,60. Saving and reopening the file, it shows 47,60 without needing any recalculation.

I do not know if there is a bug here.

Best regards. JBF
Comment 4 Tim R 2016-03-25 10:30:32 UTC
Using these numbers: 
11.8913
11.7535
11.8765
4.0325
8.0689
-------
47.6227

I set Precision As Shown = True. No matter what # of decimals I show, the sum still shows 47.6227. What should happen, is that as I decrease the # of decimals shown for the summed numbers, the sum should become 47.6240 then 47.6200 then 47.7000. But it does not change. 

I went backwards using Portable Apps versions to see where it worked correctly.

5.1.2.1 Full Verison -- no
5.0.4 Portable Apps -- no
4.4.7 Portable Apps -- no
4.3.7 Portable Apps -- no
4.2.8 Portable Apps -- no
4.1.6 Portable Apps -- it works
4.0.6 Portable Apps -- it works
3.6.5 Portable Apps -- it works
Comment 5 Tim R 2016-03-25 10:31:31 UTC
Created attachment 123831 [details]
Precision as Shown
Comment 6 Tim R 2016-03-25 10:32:33 UTC
Created attachment 123832 [details]
Precision as Shown
Comment 7 m.a.riosv 2016-03-25 11:48:24 UTC
Created attachment 123838 [details]
Sample showing how it works

Seems the issue is with SUM(), SUMPRODUCT(), maybe others functions, that doesn't take the value as show when cells have exclusively numbers without '=' in front.

Changing decimal places for 'B4:B8' doesn't change the sum, but it does changing the decimal places for F4:F8
Comment 8 Juan Pedro Reyes Molina 2016-10-31 14:20:41 UTC
*** Bug 103600 has been marked as a duplicate of this bug. ***
Comment 9 QA Administrators 2017-11-01 22:12:11 UTC Comment hidden (obsolete)
Comment 10 Juan Pedro Reyes Molina 2017-11-02 11:29:46 UTC
Bug confirmed

Versión: 5.4.2.2 (x64)
Id. de compilación: 22b09f6418e8c2d508a9eaf86b2399209b0990f4
Subprocs. CPU: 4; SO: Windows 6.19; Repres. IU: predet.; 
Configuración regional: es-ES (es_ES); Calc: group
Comment 11 QA Administrators 2018-11-03 03:50:02 UTC Comment hidden (obsolete)
Comment 12 Jean-Baptiste Faure 2018-11-03 07:38:10 UTC
Still reproducible with current master.
Rewording of the summary to make it more precise. See comment #7.

Best regards. JBF
Comment 13 Jean-Baptiste Faure 2018-11-03 07:40:14 UTC
From comment #4 seems to be a regression since version 4.2.8

Best regards. JBF
Comment 14 himajin100000 2018-11-03 07:57:54 UTC
source code pointer for possible cause
https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/interpr6.cxx?r=18ff25ff#826
Comment 15 Xisco Faulí 2018-11-06 11:55:37 UTC
Regression introduced in range https://cgit.freedesktop.org/libreoffice/core/log/?qt=range&q=4b4ca8030285bd66526ff5bb2b6ea5a75a6c6bc7..d59024b652ccfaf7247da113ec36788fe260de74

@Eike, I thought you could be interested in this issue...
Comment 16 luca.manganelli 2019-09-06 09:46:13 UTC
Created attachment 153980 [details]
Another example with bug

Hello, in LibreOffice Calc 6.2.6 there is still this bug. I've attached a new simple file, so you can see.
Comment 17 Eike Rathke 2019-09-06 11:21:53 UTC
Taking.
Comment 18 Commit Notification 2019-09-06 15:27:49 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/4321d8c8b09fd880a18b4106cdd1f90acc18a861%5E%21

Resolves: tdf#98844 calculate as shown with SUM() again

It will be available in 6.4.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 19 Eike Rathke 2019-09-06 15:29:38 UTC
Pending review
https://gerrit.libreoffice.org/78727 for 6-3
https://gerrit.libreoffice.org/78728 for 6-2
Comment 20 Commit Notification 2019-09-06 18:03:57 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/2b591a40b0f6894531350ccb733abef3c3e1d9bf%5E%21

Related: tdf#98844 CreateMatrixFromDoubleRef() for bCalcAsShown ScCellIterator

It will be available in 6.4.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 21 Xisco Faulí 2019-09-17 08:54:29 UTC
Verified in

Version: 6.4.0.0.alpha0+
Build ID: c56bf1479cc71d1a2b0639f6383e90c1f7e3655b
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; 
Locale: ca-ES (ca_ES.UTF-8); UI-Language: en-US
Calc: threaded

Sum is displayed as shown after recalculating.

@Eike, thanks for fixing this issue!
Comment 22 Commit Notification 2019-09-17 08:56:34 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-3":

https://git.libreoffice.org/core/+/23640b11ef01840e4bf25134e18c7a0e80323099%5E%21

Resolves: tdf#98844 calculate as shown with SUM() again

It will be available in 6.3.3.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 23 Commit Notification 2019-09-17 08:56:44 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-2":

https://git.libreoffice.org/core/+/9b600f1aeb861219bf70a2941e3899ee545eff98%5E%21

Resolves: tdf#98844 calculate as shown with SUM() again

It will be available in 6.2.8.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.