Bug 122264 - SUM reports incorrect value
Summary: SUM reports incorrect value
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
Keywords: bibisected, regression
Depends on:
Blocks: Calculate
  Show dependency treegraph
Reported: 2018-12-21 14:44 UTC by Frederic Parrenin
Modified: 2019-01-25 15:00 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:

.xlsm file to reproduce the problem (15.85 KB, application/vnd.ms-excel.sheet.macroEnabled.12)
2018-12-21 14:44 UTC, Frederic Parrenin
.xlsm file to reproduce the problem (23.64 KB, application/vnd.ms-excel.sheet.macroEnabled.12)
2018-12-21 15:13 UTC, Frederic Parrenin
sheet1.xml cell d6 (44.40 KB, image/png)
2018-12-21 17:27 UTC, Oliver Brinzing
excel protected view (26.04 KB, image/png)
2018-12-21 17:32 UTC, Oliver Brinzing

Note You need to log in before you can comment on or make changes to this bug.
Description Frederic Parrenin 2018-12-21 14:44:55 UTC
Created attachment 147753 [details]
.xlsm file to reproduce the problem

Steps to reproduce:
- open the attached .xlsm file in Calc
- go to D6
=> you can see that D6=SUM(E6:AJ6)/8 but calc reports '0' as value. This is incorrect.
- now copy D5 and paste in D6
=> now the correct value is reported, while the formula is the same than before.
Comment 1 Frederic Parrenin 2018-12-21 15:13:14 UTC
Created attachment 147758 [details]
.xlsm file to reproduce the problem

A more explicit way to reproduce the problem:
- open the attached .xlsm file
- go to D7 and type =D6
- now go to E6 and type 2
=> D6 stays at 0 while it should be 0.25
Comment 2 Durgapriyanka 2018-12-21 16:45:41 UTC
Thank you for reporting the bug. I can confirm that the bug is present in

Build ID: 3c964980da07892a02d5ac721d80558c459532d0
CPU threads: 2; OS: Windows 6.1; UI render: default; VCL: win; 
TinderBox: Win-x86@42, Branch:master, Time: 2018-12-12_02:07:45
Locale: en-US (en_US); UI-Language: en-US
Calc: threaded
Comment 3 Oliver Brinzing 2018-12-21 17:27:59 UTC
Created attachment 147763 [details]
sheet1.xml cell d6

(In reply to Frederic Parrenin from comment #0)

please check "Recalculation on File Load" settings in menu 
i guess your selection is "Never recalculate".

and attached xmls has value "0" stored inside "sheet1.xml".

a full hard recalc Ctrl+Shift+F9 will update the D6 to 8,625.

IMHO this is not an issue.
Comment 4 Oliver Brinzing 2018-12-21 17:32:31 UTC
Created attachment 147764 [details]
excel protected view

opening attached file with ms excel 2016 will open in 
protected view and show "0" too. 
D6 value will change as soon as i allow editing
Comment 5 Frederic Parrenin 2018-12-21 17:53:14 UTC
Olivier, please see my second comment. The calculation is not updated even if you modify the sheet.
Comment 6 Oliver Brinzing 2018-12-21 18:20:52 UTC
(In reply to Frederic Parrenin from comment #5)
> Olivier, please see my second comment. The calculation is not updated even
> if you modify the sheet.

i can confirm the problem with your second example file.
one has to use ctrl+shift+f9 to get the correct result.

adding issue to "(Calculate) - [META] Calculate bugs and enhancements"

@Xisco: is it possible that someone can take this ?
Comment 7 Hiromi Kuramoto 2018-12-25 06:25:00 UTC
Hello, I confirmed your reporting both bugs in also macOS Sierra.

Build ID: 86daf60bf00efa86ad547e59e09d6bb77c699acb
CPU threads: 4; OS: Mac OS X 10.12.5; UI render: default; 
Locale: en-US (ja_JP.UTF-8); Calc: group threaded
Comment 8 Xisco Faulí 2018-12-26 12:41:53 UTC
Reproduced back to

Version (Build ID: efca6f15609322f62a35619619a6d5fe5c9bd5a)

but not in

LibreOffice 3.5.0 
Build ID: d6cde02
Comment 9 Buovjaga 2019-01-25 15:00:00 UTC
Bibisected on Linux with 43all to https://gerrit.libreoffice.org/plugins/gitiles/core/+log/bed0447cefb949fc77cfde7543397d96590082ba..a581d31b227623e09d2970a91214fda398f98eda

It has some Calc / xlsx changes, but not sure which could be the cause.