Created attachment 131775 [details] Minimal example showing the incorrect behaviour When you have 100 or more contiguous cells in one column containing a sum formula spanning multiple sheets (in excel, this is called a "3d reference"), only the data of the first sheet's range is summed up. How to reproduce: Use the attached example, or do this: - create new spreadsheet document - add one new sheet (so we now have Sheet1 and Sheet2) - enter the value "1" into Sheet1.B1 and Sheet2.B1 - enter the formula "=SUM(Sheet1.B1:Sheet2.B1)" into Sheet1.A1 - Sheet1.A1 now shows the value "2", as expected - select Sheet1.A1 and drag the formula down to Sheet1.A100 (without letting go!) Expected result: Sheet1.A1 still shows the value "2" Observed result: Sheet1.A1 shows the value "1" instead of "2" Further Info: - "Recalculate", "Recalculate Hard", or (save, close, re-open the document) do not help - modify any of the formulas on Sheet1 so it does not match the pattern of the others (e.g.: in Sheet1.A100, change "B100" to "B101" in either or both of the cell references), then "Recalculate Hard", and the problem goes away. Revert the modification, and it still works ok - until you save, close and re-open the document. - if you drag the formula to A99 only, then pick up the drag square again and drag to A100, A1 will still show "2" as expected, but will not update if you change the value on Sheet2.
I also tried this with the functions AVERAGE and MAX. They do not appear to be affected. In Libreoffice 5.0.6.2, the problem does not occur.
The current version from the libreoffice website (5.3.0.3, Build ID: 7074905676c47b82bbcfbea1aeefc84afe1c50e1, Flatpak version) still shows the incorrect behaviour of the SUM function.
Confirmed in -Versión: 5.3.1.1 Id. de compilación: 72fee18f394a980128dc111963f2eefb05998eeb Subpr. de CPU: 1; SO: Windows 6.1; Repr. de IU: predet.; Motor de trazado: HarfBuzz; Configuración regional: es-ES (es_ES); Calc: group -Version: 5.4.0.0.alpha0+ Build ID: d3676ceeec55a41337ce5e6bc596f4f100d0638e CPU threads: 4; OS: Linux 4.8; UI render: default; VCL: gtk2; Locale: ca-ES (ca_ES.UTF-8); Calc: group -Version: 5.2.0.0.alpha1+ Build ID: 5b168b3fa568e48e795234dc5fa454bf24c9805e CPU Threads: 4; OS Version: Linux 4.8; UI Render: default; Locale: ca-ES (ca_ES.UTF-8) but not in Version: 5.0.0.0.alpha1+ Build ID: 0db96caf0fcce09b87621c11b584a6d81cc7df86 Locale: ca-ES (ca_ES.UTF-8)
Maybe due to OpenCL or Software Interpreter that don't handle 3D ranges and get triggered with rows>100. Could you try to disable OpenCL and/or Software Interpreter under Tools-Options-LibreOffice-OpenCL?
In my installation, "OpenCL" was disabled, and apparently I cannot enable it. (I assume this is because I'd need to install additional libraries for this to work?) Disabling "Software Interpreter" and restarting LibreOffice does fix the problem for me.
So yes, that confirms my assumption. OpenCL being completely disabled means that your driver either is already blacklisted or the tests that are run on first startup discovered it's not usable.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=ca1f051972946b24cb6658143fb0d0ac3587a988 Resolves: tdf#106459 3D reference can't be handled as vector reference It will be available in 5.4.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review https://gerrit.libreoffice.org/35051 for 5-3 https://gerrit.libreoffice.org/35052 for 5-2
I'll retest when I see a new daily build for platform Linux x86_64.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=2a45464d7c9ee9c7bb5af2bc26dcd9a99bb53ef8&h=libreoffice-5-2 Resolves: tdf#106459 3D reference can't be handled as vector reference It will be available in 5.2.7. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-3": http://cgit.freedesktop.org/libreoffice/core/commit/?id=b16868ab2f7f3f0e09d68faba75d16fff1d851c1&h=libreoffice-5-3 Resolves: tdf#106459 3D reference can't be handled as vector reference It will be available in 5.3.2. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Markus Mohrhard committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=01667d040d9280ee2d918a4bc4a5147e870c039c add test for tdf#106459 It will be available in 5.4.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
finally got around to downloading a daily build of 5.2.7 . fix confirmed: Formula result is now computed correctly even with either OpenCL or "Software Interpreter" enabled.