Created attachment 128297 [details] file illustrating the bug In the attached file, SUMPRODUCT returns wrong values. Not only that, but the same formula returns two different values in two cells: T19 = 20.0025562025 AE19 = 18.7191658181 (correct value) First observed on 5.1.5, still there after upgrade to 5.1.5. One more strange thing: if I copy formulas from AE19 to the cells below, the formulas remain correct if I copy to less than 100 cells, but switches to the wrong one if I copy to more than 100 cells (pass row AE118). This happens both with copy & paste and using mouse (selecting AE19 and dragging down). I use Norwegian locales with comma as a decimal separator, in case it should matter.
You have to recalculate your data, Ctrl+Shift+F9. Please have a look at menu Tools > Options > LibreOffice Calc > Formula. Perhaps the default settings in section "Recalculation on File Load" do not work well for you. And look in menu Data > Calculate. Is AutoCalculate enabled?
I agree it looks like an updating issue. When I press Ctrl+Shift+F9, the computed statistics (T10:X18) get updated (the mean is equal to zero as it should). However, the values themselves (T19:X1019) are not updated - for example, T19 is still 20.0. To update this, I have to, for example, add '+1' to the formula (which changes the value of T19 to 19.7 and then remove it again, to get the correct value. > Please have a look at menu Tools > Options > LibreOffice Calc > Formula. > Perhaps the default settings in section "Recalculation on File Load" do not work well for you. The "Recalculation on File Load" section has only items for "Excel 2007 and newer" and "ODF spreadsheet (not saved by LibreOffice)", neither of which applies since the file was created in LibreOffice. Anyway, I changed both to "prompt" and do not get one when I open the file > And look in menu Data > Calculate. Is AutoCalculate enabled? Yes, AutoCalculate is enabled.
Update to my previous comment: The +/-1 trick works for a single cell, but when I try to do copy the formulas to the whole column, I end up again with the wrong values .. this is frustrating..
Reproducible: Version: 5.2.3.2 (x64) Build ID: c019706a50de7fcb07d0d528b09b46a887562beb CPU Threads: 4; OS Version: Windows 6.19; UI Render: GL; Locale: en-GB (es_ES); Calc: CL Disabling Menu/Tools/Options/LibreOffice/OpenCL, works fine for me. Changing to a locale with dot as separator like English-UK doesn't solve the issue.
I can confirm that disabling OpenCL (setting the minimum data size for OpenCL to a very large number) removes the problem. This also explains the strange behaviour with copy&paste from the original description, since the limit for OpenCL was set to 100.
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
what the fu.. ?? CL-limit 100: 100 correct results then scrap, CL-limit 1000: 1000 correct results then scrap, programs like this are given to, and used by!, 'dummies', and even for military purposes ... nuked the wrong city? 'may be your CL-limit was too low, didn't you know you have to set it to an app. value?, your fault.', settings may affect the performance, but never the correctness of results! bug - may be - 'related' to the general shared formula problems? repro (CL: T19 20,002556202506, no-CL: T19 18,7191658181081) with: Version: 6.2.8.2 (x64) Build ID: f82ddfca21ebc1e222a662a32b25c0c9d20169ee CPU threads: 8; OS: Windows 6.1; UI render: default; VCL: win; Locale: de-DE (de_DE); UI-Language: en-US Calc: CL and also with - independent of setting for 'CL'!!! Version: 6.5.0.0.alpha0+ (x64) Build ID: 9ab43aebad67383057d2cc3f754ce2193fa78b4e CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: GL; VCL: win; Locale: de-DE (de_DE); UI-Language: en-US Calc: three years, major importance, no progress ... be ashamed ... b.
from some 'digging into' ... file provided has at least one cell which has a wrong value stored in it, T20: <text:p>20,0025562025061</text:p> (the content.xml file stores the formula and the value / string). perhaps autocal was off, or the version used was buggy. that makes any investigation difficult as plenty changes on sheet or in settings will correct the problem - by triggering recalculation - without having been the cause for it. values brought to screen differ acc. to the settings in 'formula - recalculate on load' (despite that normally shouldn't affect this file (neither 'ex$el' nor 'not saved with LO'), but it does, at least with 6.4.0.1). questionable behaviour of 'recalculate on load' is discussed elsewhere. for me with 6.4.0.1: 'always recalculate' comes up with refreshed and correct values, 'prompt user' doesn't prompt but recalculates, correct, 'never recalculate' comes up with wrong - stored - values. for further investigation a new file with new description is needed, or the original system and settings where the original bug was 'found', i doubt that being accessible. thus pls. recheck and set to 'wfm - provided file was buggy' or similar. reg. b.
from some more digging: 1.) wrong value in AE19: is stored in the file provided and needs recalc <F9>, hard recalc <ctrl-shift-F9> or 'recalc on load' to be corrected, 2.) wrong values when dragging or copying down AE19 already occured in ver. 5.0.0.1, that's not offered in the list, thus setting 'first affected' to 5.0.0.5, it's been less a problem of SUMPRODUCT but of openCL and 'shared formulas', dependent of openCL or 'threaded' or both being activated and copying the same formula in 100+ cells, it was (is) dependent of copying into 100+ rows because of a hardcoded threshold of 100 for parallelized computing (openCL or 'threaded') to kick in if its enabled in the config, 2. is gone in ver. 7.0.0.0.a0+ (and some before, 6.2.8.2 was still buggy), thus setting to wfm, above does not! say shared formulas or openCL are clean in total, it only means that this particular facet is gone, ver. tested: Version: 7.0.0.0.alpha0+ (x64) Build ID: 10e20a77ce302a0475a661ad1886f2ca83c55f3f CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: Skia/Raster; VCL: win; Locale: de-DE (de_DE); UI-Language: en-US Calc: CL reg. b.