Bug 124669 - Error in the function SUM (LibreOffice Calc)
Summary: Error in the function SUM (LibreOffice Calc)
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.0.7.3 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: regression
Depends on:
Blocks: Function-Sum
  Show dependency treegraph
 
Reported: 2019-04-11 05:06 UTC by Evilson
Modified: 2020-02-01 05:33 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
The file where the error has occurred. The cell with the error was highlighted. (13.35 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-04-11 05:08 UTC, Evilson
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Evilson 2019-04-11 05:06:35 UTC
Description:
The file with the error was attached.
Two sumations of the same values with different results.
I couldn't reproduce the event.

Actual Results:
If the cell with the error is changed (for example: if a character in the formula is deleted and retyped), the cell returns to normal after type Enter.

Expected Results:
Sumations of the same values with same results.


Reproducible: Couldn't Reproduce


User Profile Reset: No



Additional Info:
Versão: 6.0.7.3
ID de compilação: 1:6.0.7-0ubuntu0.18.04.2
Threads da CPU:4; SO:Linux 4.15; Realizador da interface: padrão; VCL: gtk3; 
Local: pt-BR (pt_BR.UTF-8); Calc: group
Comment 1 Evilson 2019-04-11 05:08:42 UTC
Created attachment 150673 [details]
The file where the error has occurred. The cell with the error was highlighted.
Comment 2 Winfried Donkers 2019-04-11 11:37:16 UTC
Is it possible that you have 'auto-calculate' switched off?
You can find this setting in menu Data - Calculate - Auto calculate.

If that is the case, then changing anything in range A4:D6 will not lead to recalculation of the formula in E6. Changing anything in E6 or entering anew formula in e.g. F6 will cause (re)calculation.
Comment 3 m.a.riosv 2019-04-11 23:12:26 UTC
I think 'Autocalculate' it's a per file option.

WFM
Version: 6.2.3.1 (x64)
Build ID: 9ba025bafb03b962c34687cf87806cc03a3a7436
CPU threads: 4; OS: Windows 10.0; UI render: GL; VCL: win; 
Locale: es-ES (es_ES); UI-Language: en-US
Calc: CL
Comment 4 b. 2019-04-11 23:46:21 UTC
repro with ver: 

Version: 6.3.0.0.alpha0+ (x64)
Build ID: 35d9a2618dc0116378ab795a7b9277d248c5afd4
CPU threads: 8; OS: Windows 6.1; UI render: default; VCL: win; 
TinderBox: Win-x86_64@42, Branch:master, Time: 2019-04-05_04:55:04
Locale: de-DE (de_DE); UI-Language: en-US
Calc: *not treaded*, 

OS: win7 pro x64, 

on file load E6: 100336,5 F6: 79936,50

despite autocalculate is! on!

'threaded' not tested, 

'correctable' with forced recalc (ctrl-shift-F9), but persistent over e.g. changes in the format (decimals) of E6, F6, 

but *not!* repro with ver: 

Version: 4.1.6.2
Build ID: 40ff705089295be5be0aae9b15123f687c05b0a, 

on file load E6: 79936,50 F6: 79936,5

thus -> new, regression, 

i assume the fault is somewhere around the shared formula / autocalculate issues, 

reg. 

b.
Comment 5 Winfried Donkers 2019-04-12 06:06:50 UTC
My question re auto-calculate in comment 2 is about the use of Calc at the time the cells where filled with formulas and/or its contents altered.
Once a document with a non-recalculated formula cell is saved, another setting becomes important : Options - Calc - Formula - Recalculate on opening document.
When this setting is on, the document in  attachment 150673 [details] opens with the correct value in cell E6. 
When this setting is of the document in  attachment 150673 [details] opens with the incorrect value in cell E6 (the value as stored in the document).

I still think there is nothing wrong with Calc, but that the document was saved with a non-recalculated cell E6, i.e. with an incorrect value in E6.

To all that opened the document in attachment 150673 [details] : please check the setting for recalculate on opening document and include that in your reports.

Also: can any one reproduce the behaviour from a new, blank, document?
Comment 6 b. 2019-04-12 22:34:48 UTC
can confirm comment 5, 

funnily in my system the settings to recalculate on load are specialised / restricted to 'Excel 2007 and newer', and 'ODF spreadsheet (not saved with LibreOfficeDev', thus normally shouldn't make a difference ... may be right as we don't know with which program the file was saved, 

checked with ver 4.2.... too, with 'recalculate' correct results, without shown number doesn't fit with formula. 

two points: 

- it would be nice to have a warning towards the user whenever (s)he's dealing with files that contain deviations between formula and formerly stored values, 

- the comment 'odf spreadsheet (not saved with libreoffice)' looks outdated, the setting produces different evaluation of the file even after saving it with a different name from inside calc, thus neither 'excel' nor 'not saved with libreoffice' are matched ... despite that calc does! do a new calculation of the values with this option set, and doesn't if not set. someone should check under which conditions recalc is triggered, check if that's the intended logic, and either correct the handling of files or the comments in the setting dialog. 

reg. 

b.
Comment 7 Winfried Donkers 2019-04-13 07:29:37 UTC
Steps to reproduce the 'problem':
start with new Calc document;
disable 'autocalculate' ;
enter 1 in cell A1 and 2 in cel A2;
enter '=SUM(A1:A2)' in cell A3;
(A3 shows 3);
change value in A2 to 4;
(A3 still shows 3, autocalculate is off);
enter '=SUM(A1:A2)' in cell A4;
(A3 shows 3, A4 shows 5);
save document.

When opening this document with recalculate on opening is off, it will still show 3 in A3 and 5 in A4.
When opening this document with recalculate on opening is on, it will show 5 in A3 and 5 in A4.

This is all behaviour as it is supposed to be, setting the status of this bug to RESOLVED/NOTABUG.
Comment 8 Winfried Donkers 2019-04-13 07:32:26 UTC
(In reply to b. from comment #6)
 
> two points: 
> 
> - it would be nice to have a warning towards the user whenever (s)he's
> dealing with files that contain deviations between formula and formerly
> stored values, 
> 
> - the comment 'odf spreadsheet (not saved with libreoffice)' looks outdated,
> the setting produces different evaluation of the file even after saving it
> with a different name from inside calc, thus neither 'excel' nor 'not saved
> with libreoffice' are matched ... despite that calc does! do a new
> calculation of the values with this option set, and doesn't if not set.
> someone should check under which conditions recalc is triggered, check if
> that's the intended logic, and either correct the handling of files or the
> comments in the setting dialog. 

You are right in your observations. Could you create two new bug reports for each point, as each has a different cause/solution (the first is programmatically, the second textually)?
Comment 9 b. 2020-02-01 05:33:40 UTC
@Wilfried

> Could you create two new bug reports for each point, 

done so in #124783, #124784

reg. 

b.