Created attachment 119471 [details] partial screen shot of the same situation; original without 0 and incorrectly positive, test with added 0 on next line and correct negative result result strange bug found found since last version update, in a calc file, used for many years (bank account, the doc itself and its formulas can be considered as improved). Column D is for incomes;Column E is for outcomes;(hidden) column F is delta result of =D-E when discovered this bug, I had 4 lines with only outcomes, but the results was ... positive. Evaluation by small formula helper was correct (negative) In a further line, if i put a 0 instead of empty cell in INcome, the 4 results turned to negative; if delete the 0, the four lines turned back to positive. Finally, i have set a space instead of the 0, making errors on all the followings lines, the delete back to an empty cell and ... everything back to normal (delta negative when outcome set, positive when income set). Strange, isn't it ? It seems that it's only happens when there is no more "incomes" in further lines, so the bug remains on lasts "outcomes" lines. I had to go back on every sheet ending with outcome lines and doing this space-enter-then-delete-content-of-cell woorkaround to get back correct values over the whole workbook.
Hi @Dude76, thanks for reporting. Please could you attach a sample file, as minimal as possible (remember this is a public site) to reproduce the issue.
Created attachment 119473 [details] light file sample reproducing the problem i have strongly enlight the original file values in F to line 39 (and to 60 if they were not empty) are OK and negative (outcomes) values from line 61 to 63 are OK and positives (incomes) following values, with no more incomes later, avec positive whereas they are outcomes, so it is KO ! i have also noticed that there are impact (due to "false" positive values) on other dependants formulas results (ex : J64) i have put comments in the file too.
yes, finaly i can; I believed that my previous "workaround" had definitively solved the problem but not, new values produced the same problem, and, worst, it had impacts the previous ones too :(
F64 =D64-E64 = 0 - 665 = -665 It's correct in Version: 5.1.0.0.alpha1+ Build ID: 8273350ff48f198efc9dc9c5de5519b8cbdc0cb3 TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2015-10-07_05:54:26 It's incorrect in 5.0.2.2 (x64), win7, but correct after hard recalc (ctrl+shift+f9). Try to do hard recalc (ctrl+shift+f9).
I did it. Bad results. The previous results that were "corrected" by the "workaround" are now bad. If with the same version you don't have the same bahavior, maybe it could indicate a corrupted installation ? I will uninstall / reboot / install. I didn't notice the problem in the previous version.
Well, missed. re-download the installer, uninstall, reboot, install, the problem remains. other point noticed : when i put/delete a 0 below, it changes the value in F (as previously noticed) but it doesn't changes in cascades the other values. ex in the provided sample: D74: 0 -> F72=>-500 but G72 doesn't refresh the calculated value (could be done by forcing but, well, automatic re-computing is expected)
For my trials the issue is in relation with the OpenCL: a) If it is active (Menu/Tools/LibreOffice/OpenCL - OpenCL options), entering zero in D72 produces -500 in F72, deleting the zero on D72 produces 500 in F72, wrong result. b) If it is deactivated, entering zero in D72 produces -500 in F72, deleting the zero on D72 produces -500 in F72, right result. Reproducible: Win10x64 Version: 5.0.3.1 (x64) Build ID: fd8cfc22f7f58033351fcb8a83b92acbadb0749e Version: 5.1.0.0.alpha1+ (x64) Build ID: 09fc6fef2d03ca8558dd6f0eec45d61ceb282cb5 TinderBox: Win-x86_64@62-TDF, Branch:MASTER, Time: 2015-09-27_22:33:48 Last working for me: Version: 4.4.7.0.0+ Build ID: 2ca166ebbe2adbd8f129403c7f8e5646c70ab67d TinderBox: Win-x86@51-TDF, Branch:libreoffice-4-4, Time: 2015-10-07_06:28:19 Really a very bad issue for a spreadsheet.
Really thanks (and congrats) for qualifying the origin of the bug. I feel less silly :) I confirm the behavior following the OPENCL setting on my file. Good luck/courage for solving it. Bests regards.
*** Bug 94496 has been marked as a duplicate of this bug. ***
*** Bug 94540 has been marked as a duplicate of this bug. ***
.
Sorry, but would it be possible to get an even simpler sample document that has just one simple description that says explicitly "the cell XX should be X, but with OpenCL turned on, it is Y" I find it hard to parse the comments in the existing Sample.ods...
Or actually, comment #7 is clear enough. (Empty or numeric zero in D72 has different impact on F72 depending on whether OpenCL is on or not.) Will use that to verify any fix I come up with.
Created attachment 119605 [details] Reduced sample file Added a reduced sample file Remarkable, for verify the issue, on the reduced sample file with only a few data, reduce the value in: Menu/Tools/Options/LibreOffice calc/Formula - Detailed calculation settings - Custom - Minimum data size for OpenCL use to 2. Introducing a zero and deleting it on D3 changes the result in E3 from -500 to 500. With OpenCL disable result in E3 is always -500.
Tor Lillqvist committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=bdef079b39d273d2985d360a52597672c96b43fc tdf#94924: Fix handling of empty cells in OpenCL subtraction It will be available in 5.1.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.
Tor Lillqvist committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=d67262fb540114b2c29d19cfd4075cc7faa20d74 tdf#94924: Fix handling of empty cells in OpenCL division It will be available in 5.1.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.
Tor Lillqvist committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=5e0e953f8f8fc5b27db8421ba15e33cfa664fb7a tdf#94924: Return correct #DIV/0! error from AVERAGE in the OpenCL case It will be available in 5.1.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.
Tor Lillqvist committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=03eae494cfdb0c75188e6c2c85a4b59acba0ef12 tdf#94924: Return correct result 0 from OpenCL MIN and MAX when all args empty It will be available in 5.1.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.
Tor Lillqvist committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=f10be151884d72b632547b570812759a67fd5c46 tdf#94924: Add a more systematic OpenCL unit test It will be available in 5.1.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.
Tor Lillqvist committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=e9caad9b5f39a84e24d82421cc8f360dcc1116ed tdf#94924: Fix thinko from bdef079b39d273d2985d360a52597672c96b43fc It will be available in 5.1.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.
Tor Lillqvist committed a patch related to this issue. It has been pushed to "libreoffice-5-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=b76c450eb7f6517fae24a0d7ea6431663959adf3&h=libreoffice-5-0 tdf#94924: Fix handling of empty cells in OpenCL subtraction It will be available in 5.0.4. 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.
Tor Lillqvist committed a patch related to this issue. It has been pushed to "libreoffice-5-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=958ef1a5bff0b7d0879a4ae34a5e7f27eadd903b&h=libreoffice-5-0 tdf#94924: Fix handling of empty cells in OpenCL division It will be available in 5.0.4. 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.
Tor Lillqvist committed a patch related to this issue. It has been pushed to "libreoffice-5-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=a6fa35ab0efc1d0f0d74e70898cc31be9d672190&h=libreoffice-5-0 tdf#94924: Return correct #DIV/0! error from AVERAGE in the OpenCL case It will be available in 5.0.4. 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.
Tor Lillqvist committed a patch related to this issue. It has been pushed to "libreoffice-5-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=127c268387e25a1f1c4b75b5991a83d383be7b92&h=libreoffice-5-0 tdf#94924: Return correct result 0 from OpenCL MIN and MAX when all args empty It will be available in 5.0.4. 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.
Tor Lillqvist committed a patch related to this issue. It has been pushed to "libreoffice-5-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=9db43e72088faa8326ca941b84d422726448293a&h=libreoffice-5-0 tdf#94924: Add a more systematic OpenCL unit test It will be available in 5.0.4. 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.
Thank you very much @Tor, for such a quick resolution. Verified: Win10x64 Version: 5.1.0.0.alpha1+ Build ID: 186f32f63434e16ff5776251657f902d5808ed3d-GL TinderBox: Win-x86@39, Branch:master, Time: 2015-10-16_09:42:47
Hello What about Bug 94540 - EDITING: VLOOKUP wrong result #VALUE in CALC 5.0.2.2. it was marked as duplicate of this bug. Disabiling OpenCL performs well, but if activated it displays #VALUE Tested today with Version: 5.1.0.0.alpha1+ Build ID: d9cf47449e88032803aa4fa3429cd607a074d5ad-GL TinderBox: Win-x86@39, Branch:master, Time: 2015-10-17_11:55:47 Locale: es-MX (es_MX) On Windows 10.
Created attachment 119707 [details] VLOOKUP wrong result test file
Sorry, but the instructions on Sheet3 of that document don't work. They say at the first step that "correct result displays" but don't say what that correct result should be. (For me, if I follow the instructions exactly, L2 on the Sheet3 displays #N/A. I assume that is not what is expected?) Please, if you want to provide a test document, just provide a sheet where doing a Ctrl-Shift-F9 after toggling "Tools:Options:OpenCL:Allow use of OpenCL" changes calculation results. That is all that is needed. No need to describe multiple steps of saving, loading, copying and pasting. (Having over 100 copies of a formula in sequential rows in a column will make OpenCL be used, as long as the formula uses only operators and functions from the OpenCL-enable-subset.)
I assume the problem in the instructions is that the formula should use Movtos.A2, not just A2? Then I see the issue.
Anyway, I am not sure if we want to mark all problems in OpenCL as duplicate of this bug or not? Clearly this VLOOKUP issue is not the same as that which affected subtraction, for instance.
Tor Lillqvist committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=476bef70f1d9fd58b29a1f6fb95e54567b031acf tdf#94924: If we can't handle strings, don't try to then It will be available in 5.1.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.
I reported a bug, which caused a wrong calculation (see bug 94271). Turning off OpenCL solves the issue. Can you please check whether your solutions solves the issue in bug 94271 as well. Can I be of any help ?
*** Bug 94271 has been marked as a duplicate of this bug. ***
Tor Lillqvist committed a patch related to this issue. It has been pushed to "libreoffice-5-0-3": http://cgit.freedesktop.org/libreoffice/core/commit/?id=2eac5c406f10f10c943d0c128dfe6a5db3972e1a&h=libreoffice-5-0-3 tdf#94924: Fix several OpenCL problems It will be available in 5.0.3. 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.
Seems like a bibisect isn't needed here as a commit has already been pushed. Can this be closed as FIXED?
I think so yes.
*** Bug 97238 has been marked as a duplicate of this bug. ***
Tor Lillqvist committed a patch related to this issue. It has been pushed to "libreoffice-5-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=6f50edb1aabaf9de37782e63abd109e2276bd0c4&h=libreoffice-5-0 tdf#97150: tdf#94924: If we can't handle strings, don't try to then It will be available in 5.0.6. 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.
Tor Lillqvist committed a patch related to this issue. It has been pushed to "libreoffice-5-0-5": http://cgit.freedesktop.org/libreoffice/core/commit/?id=320246d24d8a07cdb6b8400e8afa48696cc39343&h=libreoffice-5-0-5 tdf#97150: tdf#94924: If we can't handle strings, don't try to then It will be available in 5.0.5. 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.