Bug 127800 - Libreoffice does not recalculate sum
Summary: Libreoffice does not recalculate sum
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.1 all versions
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Calculate
  Show dependency treegraph
 
Reported: 2019-09-26 17:58 UTC by jusebfer
Modified: 2020-02-16 12:48 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
ods file with the bug (10.12 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-09-26 18:01 UTC, jusebfer
Details

Note You need to log in before you can comment on or make changes to this bug.
Description jusebfer 2019-09-26 17:58:53 UTC
Description:
I have an .ods file. In one cell doing a sum the sum is incorrect. When I add a sheet or hit F9 it recalculates it, but is seems not to have recalculated when the value changed.  Autocalculate is on in the cell.

Steps to Reproduce:
1. Open the file and notice it is 14.0, ie wrong
2. Hit F9 and it fixes itself


Actual Results:
sum is 14.0

Expected Results:
sum is 15.5


Reproducible: Always


User Profile Reset: No



Additional Info:
Version: 6.1.6.3
Build ID: 1:6.1.6-0ubuntu0.18.10.3
CPU threads: 8; OS: Linux 4.18; UI render: default; VCL: x11; 
Locale: en-US (en_US.UTF-8); Calc: group threaded
Comment 1 jusebfer 2019-09-26 18:01:16 UTC
Created attachment 154559 [details]
ods file with the bug

bug is in cell B2
Comment 2 m.a.riosv 2019-09-26 20:54:58 UTC
I can't repro
Version: 6.3.2.2 (x64)
Build ID: 98b30e735bda24bc04ab42594c85f7fd8be07b9c
CPU threads: 4; OS: Windows 10.0; UI render: GL; VCL: win; 
Locale: es-ES (es_ES); UI-Language: en-US
Calc: threaded

Looks like a duplicate of https://bugs.documentfoundation.org/show_bug.cgi?id=100603

Please can you test with a newer version
Comment 3 Oliver Brinzing 2019-09-28 09:43:09 UTC
repro with:

Version: 6.4.0.0.alpha0+ (x64)
Build ID: 71ef762f21ada8c25aad2183065478171e985e8c
CPU threads: 4; OS: Windows 10.0; UI render: default; VCL: win; 
Locale: de-DE (de_DE); UI-Language: en-US
Calc: threaded

for whatever reason the value "14" was saved to xml:

<table:table-cell table:formula="of:=SUM([.C2:.J2])" office:value-type="float"  office:value="14" calcext:value-type="float">
<text:p>14.0</text:p>
</table:table-cell> 

please try: 

- Menu Data/Calculate/Recalculate hard

or change:
- Menu Tools/Options.../LibreOffice Calc/Formula
  Recalculation on File load:  [Always recalculate]
Comment 4 jusebfer 2019-09-28 15:02:37 UTC
(In reply to m.a.riosv from comment #2)
> I can't repro
> Version: 6.3.2.2 (x64)
> Build ID: 98b30e735bda24bc04ab42594c85f7fd8be07b9c
> CPU threads: 4; OS: Windows 10.0; UI render: GL; VCL: win; 
> Locale: es-ES (es_ES); UI-Language: en-US
> Calc: threaded
> 
> Looks like a duplicate of
> https://bugs.documentfoundation.org/show_bug.cgi?id=100603
> 
> Please can you test with a newer version

It is not a duplicate, if I hit f9 it is fixed. However, it is unreasonable to have the user double-check every single cell for recalculation, since it should be and is configured to be automatic. 
I have updated to 
Version: 6.2.7.1
Build ID: 1:6.2.7-0ubuntu0.19.04.1
CPU threads: 8; OS: Linux 5.0; UI render: default; VCL: x11; 
Locale: en-US (en_US.UTF-8); UI-Language: en-US
Calc: threaded

 and the problem persists.
Comment 5 jusebfer 2019-09-28 15:05:30 UTC
(In reply to Oliver Brinzing from comment #3)
> repro with:
> 
> Version: 6.4.0.0.alpha0+ (x64)
> Build ID: 71ef762f21ada8c25aad2183065478171e985e8c
> CPU threads: 4; OS: Windows 10.0; UI render: default; VCL: win; 
> Locale: de-DE (de_DE); UI-Language: en-US
> Calc: threaded
> 
> for whatever reason the value "14" was saved to xml:
> 
> <table:table-cell table:formula="of:=SUM([.C2:.J2])"
> office:value-type="float"  office:value="14" calcext:value-type="float">
> <text:p>14.0</text:p>
> </table:table-cell> 
> 
> please try: 
> 
> - Menu Data/Calculate/Recalculate hard
> 
> or change:
> - Menu Tools/Options.../LibreOffice Calc/Formula
>   Recalculation on File load:  [Always recalculate]

Thank you for identifying the issue. As stated in the initial post, doing a hard recalculation solves it, but you cannot expect the user to double check every single cell in a spreadsheet with recalculation.
I went to Menu Tools/Options.../LibreOffice Calc/Formula/ Recalculation on File load and set excel 2007 and odf spreadsheet to prompt user.
But the file is neither odf nor excel 2007, so I expected nothing to change. 
Indeed, nothing happens when reloading the file.
Comment 6 QA Administrators 2019-09-29 03:08:38 UTC Comment hidden (obsolete)
Comment 7 Xisco Faulí 2019-09-30 11:07:48 UTC
Reproduced back to

Version 4.1.0.0.alpha0+ (Build ID: efca6f15609322f62a35619619a6d5fe5c9bd5a)

@Eike, what's your opinion on this one ?
Comment 8 Xisco Faulí 2019-09-30 11:09:53 UTC
@Eike, what's your opinion on this one ?
Comment 9 Eike Rathke 2019-10-01 17:48:45 UTC
As mentioned already in comment 3, the wrong value 14 is stored in the file (for whatever reason) so it has to be recalculated once (by F9 on individual formula cells, or Shift+Ctrl+F9 for all, or entering a value in the SUM's range) and saved again. There's nothing we could do else (except recalculating even LibreOffice files during load).
Comment 10 raal 2019-11-28 16:03:13 UTC
(In reply to Eike Rathke from comment #9)
> As mentioned already in comment 3, the wrong value 14 is stored in the file
> (for whatever reason) so it has to be recalculated once (by F9 on individual
> formula cells, or Shift+Ctrl+F9 for all, or entering a value in the SUM's
> range) and saved again. There's nothing we could do else (except
> recalculating even LibreOffice files during load).

Closing the bug