Created attachment 126502 [details] Spreadsheet to reproduce the issue This is not a duplicate of bug 40626 because it does not require chaining of MULTIPLE.OPERATIONS to rear its ugly head. Instead, attempting to run MULTIPLE.OPERATIONS on a set of cells which contain a simple precedence chain causes the bug to appear. An example is in the attached spreadsheet. Expected behaviour: MULTIPLE.OPERATIONS succeeds and shows numeric results, no matter whether cell D2 points to A399, A400, or any other cell above or below in A1:A1000. Actual behaviour: MULTIPLE.OPERATIONS fails with Err:522 if cell D2 points to A400 or any cell below. Err:522 messages then continue to contaminate the sheet, even if cell D2 it edited to point to A399 or above again. The only way to recover is to enter "=A399" in cell D2, save and re-open. First seen in Version: 4.3.3.2 Build ID: 430m0(Build:2) Verified as still being a problem in Version: 5.1.4.2 Build ID: f99d75f39f1c57ebdd7ffc5f42867c12031db97a It appears that this bug tickles a hardcoded limit. It makes parametric exploration of data sets impossible. There is no obvious reason why MULTIPLE.OPERATIONS would need to include a hardcoded limit for the depth of the precedence chain. What it should do is to "stuff" a value in a column or row input cell and collect the output from the formula cell once recalculation has finished.
Err:522 means a Circular reference (status bar to the right), and it happens because A1 point to D1, and A1 is referenced directly or indirectly in the A column formulas, so if D1 points to column A you have the error. If you want to work with circular reference, set up it in Menu/Tools/Options/LibreOffice calc/Calculate - Iterative references. Resolved as not a bug, please if you are not agree reopen it.
With respect, this *is* a bug, because there is no circular reference, MULTIPLE.OPERATIONS incorrectly claims that there is. In the example spreadsheet I attached: D1 precedes A1 A1 precedes A2:A1000, which includes A399 A399 precedes D2 (D2, not D1 !) This is a two-ended chain, not a circle. There is no circular reference here. The bug is that MULTIPLE.OPERATIONS *incorrectly* claims that there is a circular reference when the "Formulas" cell has more than a certain number of precedent calculations. In this example spreadsheet the number of cells that need to be evaluated to find the value of D2 are: A2 -> A399 - 398 cells A1 -> A2 - 1 cell D1 -> A1 - 1 cell Total of 400 precedent calculations. If you increase this slightly, for example by setting cell D2 to "=A1000" you will see the error occur. At this point, close the spreadsheet without saving, reopen it, and just for fun set cell D2 to "=A55". This will work. You can then bisect the range to see at what number of precedent calculations it fails. I found it to be 400 as above.
Found here https://ask.libreoffice.org/en/question/31343/lo-calc-multiple-operations-define-input/ the explanation about MULTIPLE.OPERATIONS parameters. The meaning of the parameters of MULTIPLE.OPERATIONS are 1 Cell containing the prototype of the formula 2 Cell, which is used as first variable in the prototype 3 Actual value of the first variable 4 Cell, which is used as second variable in the prototype 5 Actual value of the second variable If I'm not wrong in your formula 2 and 3 are inverted what leads to the circular reference. =MULTIPLE.OPERATIONS(D$2;$F2;$D1) D1 is used as first actual value the next it's D2 the cell for the formula. The Err:522 appears when A400 or below is referenced in D2, in A400 or below and in D2, doing a hard recalc the whole column A gets Err:522. But indeed there is a different bug, because even deleting the whole MULTIPLE.OPERATIONS formulas, Errs:522 don't disappear and the spreadsheet it's not recalculated even with hard recalc {Ctrl+Shift+F9], even deleting D1 and D2 and A1. Same issues with Aoo 4.1 Disabling OpenCl has not any effect. Menu/Tools/Detective - Trace error doesn't show anything. Please Eike could you take a look.
Hi Miguel, I created the MULTIPLE.OPERATIONS parameters in the example spreadsheet attached to this bug by: - highlighting F2:G11 - selecting Data - Multiple Operations from the menu - entering $D$2 in the "Formulas" field of the dialog box - entering $D$1 in the "Column Input Cell" of the dialog box This creates in the formulas you see, and the output is arithmetically accurate unless you exceed the magic number of 400 precedent calculations. You can verify this by, for example, entering "2" in cell D1 manually and comparing the value of A399 with the value of G3. They are the same. Similar for the other values from 1-10. If as you suggest, you were to swap the second and third parameter of each MULTIPLE.OPERATIONS formula then you would end up with incorrect results. So the spreadsheet is constructed correctly, without any circular references. The error messages really are bogus.
** 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 on a currently supported version of LibreOffice (5.4.1 or 5.3.6 https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System 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) 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: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug-20170901
Dear Qaz, 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
Still reproducible with the attached demo. Version: 6.4.2.2 (x64) Build-ID: 4e471d8c02c9c90f512f7f9ead8875b57fcb1ec3 CPU-Threads: 2; BS: Windows 10.0 Build 18362; UI-Render: Standard; VCL: win; Gebietsschema: en-GB (de_DE); UI-Sprache: de-DE Calc:
Reproduced in: Version: 7.2.0.3 / LibreOffice Community Build ID: 2a7ea282da28d665a7dc086360567b4aea27bf08 CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: threaded and Version: 7.3.0.0.alpha0+ / LibreOffice Community Build ID: adf65471e889676a600a9c6d0454c75cbd549ad3 CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US TinderBox: Linux-rpm_deb-x86_64@86-TDF, Branch:master, Time: 2021-08-13_04:44:18 Calc: threaded
Dear Qaz, 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 https://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://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug