Description: open a spreadsheet created by an Excel user. The =sum() total is not correct. Steps to Reproduce: 1. open file 2. I copy the data to rows > row 30, included totalling 3. notice that =sum totals are wrong sometimes but, the sum in the status bar is correct. Actual Results: checked in MS Excel, does not have same problem Expected Results: Totalling should be correct Reproducible: Always User Profile Reset: Yes Additional Info: [Information automatically included from LibreOffice] Locale: en-GB Module: SpreadsheetDocument [Information guessed from browser] OS: Linux (All) OS is 64bit: yes Version: 6.0.7.3 Build ID: 1:6.0.7-0ubuntu0.18.04.10 CPU threads: 4; OS: Linux 5.3; UI render: default; VCL: gtk3; Locale: en-GB (en_GB.UTF-8); Calc: group I did a clean install of Linux Mint Cinnamon 19.3 - problem is the same Same problem in LO v7
Created attachment 157912 [details] sum function not consistent
Created attachment 157913 [details] screenshot of summed column
Created attachment 157914 [details] screenshot of status bar
It is reproducible with ersion: 6.4.1.1 (x64) Build ID: 56f3c78975db08733f771c53643b5d1aa7c57567 CPU threads: 12; OS: Windows 10.0 Build 18363; UI render: GL; VCL: win; Locale: ru-RU (ru_RU); UI-Language: en-US Calc: CL It uses the cached value stored in the XLSX file: > <c r="E30" s="23" t="n"> > <f aca="false">SUM(E5:E29)</f> > <v>454</v> > </c> It depends on the default setting "Recalculation on File Load": "Excel 2007 and newer" - "Never recalculate" under Options->Calc->Formula. If, however, it is set to "Always recalculate", this is expectedly shown correct. Also doing a hard recalc on the file normalizes the calculated data. Actions in the sheet like copy and paste to other places that don't affect the formula cell e.g. in E30 will expectedly *not* recalculate its value, since SUM is not a volatile function. I don't know if this should be closed NOTABUG. Eike, what do you think?
Is not intuitive. One thing which would highlight this is if the defaults in "recalculate on file load" were auto set to "Prompt user". I work in environments were almost everyone is using MS Office. Not sure why I have never seen this before. Is it possible to do an integrity test when one opens a spreadsheet which was not created by Calc, to check whether such a recalc is necessary? I guess it could take a long time on low powered computers and users may abandon the open file unless one has a message like "File Integrity check" with a progress bar. and a suggestion that the option be changed if the user experiences this a lot.
Note that the attached file was created by LibreOffice/6.0.7.3, not MS-Excel; under some circumstances shared grouped formulas if copied/moved were not recalculated before saving (e.g. on sheet Attendance in columns C, D, E). I'm not sure if we always should recalculate documents saved with earlier LibreOffice versions or whether there's a better approach. My take is recalculate, maybe only if there are shared formula groups. Maybe even by setting only those ranges dirty (which broadcasts to dependents), on the other hand that might take longer than a forced recalc if there are a lot of formula groups.
i think in the history of the table data was added or updated while 'autocalculate' was off, the following work to identify the the error (row 31++) did not trigger a recalculation of row 30 ... funnily hard recalc can't heal the deviation in cols V:Y ... the formulas in row 30 start from row 6 instead of row 5, > checked in MS Excel, does not have same problem probably recalculate on load active in excel? > integrity test when one opens a spreadsheet which was not created by Calc won't be save, same problem can easily be constructed in calc, > version: checked back until 4.1.6.2, automatic 'recalc' always stays in the balancing act trying to avoid errors (with) or delays (without), thus: enhancement request to inform users when dealing with outdated cached values ... *** This bug has been marked as a duplicate of bug 130118 ***