Created attachment 57875 [details] Example spreadsheet with iterations My iterative spreadsheet model converges fine in MS Excel, but not in Calc. I have had this issue with many of my iterative calculations in LO (and older versions of OO) calc. And No, this isn't a case of MS Excel converging incorrectly. Please see the attached file - it calculates fine in MS Excel (albeit a little slowly) but gives error 523 in all versions of Calc.
[Reproducible] with "LibreOffice 3.3.4 - Ubuntu 11.04 (32bit) Spanish UI"
Created attachment 62227 [details] previous document, saved as pdf using msExcel 2007
Thanks for bugreport reproduced in 3.3.4 and 3.5.3 on Fedora 64 bit and in 3.5.2 on Windows 7 What is interesting: in column C all calculated. Problem appears in all remaining columns. When I delete content of cell in row 24 (name of row "Remainder after Option exercise proceeds"), all values in that column becomes calculated. Then I press Ctrl-Z, and values still calculated. And pressing F9 nothing changes. But values differs from Excel.
** 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 (4.4.1 or later): 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 your help! -- The LibreOffice QA Team This NEW Message was generated on: 2015-07-18
Yeah, erroring. Win 7 Pro 64-bit Version: 5.1.0.0.alpha1+ Build ID: fcc2415ade6ae93710bbbda9f7e163045e323105 TinderBox: Win-x86@62-merge-TDF, Branch:MASTER, Time: 2015-10-21_16:55:13 Locale: fi-FI (fi_FI)
** 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.1.6 or 5.2.3 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-20161108
yes, there is still something buggy ... funny ... odd ... strange ... curious ... weird ... with 'calc and iterations'. testing with the file submitted it's clear: - it has circular references (e.g. C5 depends on C30, C30 depends on C27, C27 depends on C5) and needs iterations to solve. the solveability and what result is 'suspected' to be the correct result is difficult, sometimes undefined, and depends heavily on start conditions. (a very simple sample: A1:'10', B1:'=A1+10', C1:'=B1-10' will show | 10 | 20 | 10 |, what if you change A1 to '=C1'? i got as well | 10 | 20 | 10 | as | 0 | 10 | 0 | as results, and both are correct, even | 100 | 110 | 100 | is correct, and you can provoke it by changing A1 from '100' to '=C1'.) implementations in spreadsheets should try to come to a 'human acceptable' solution?, that's - in most cases - achieveable with the logic and default settings in calc , but not in all. on this sample and with ver. below i see three things 'funny': - if you provoke an err. condition by switching iterations off and on again calc produces heavily deviating results, (about 2.000.000 'off' in D37:G37 while C37 likes to stuck in err:523). hard recalc shows that better results are! possible except for C37, that's trapped. - the excel results in the provided pdf have no results in D35 and D37, the '20.000.000 case', i hope that's a fault from excel. - column C being trapped in the err: condition. this quite old case shows what kohei yoshida wrote somewhere about recursions / iterations in calc: 'old big problem, nothing to be fixed overnight', behaviour independent from file format (xls or ods). tested with ver. Version: 6.4.0.1 (x64) Build ID: 1b6477b31f0334bd8620a96f0aeeb449b587be9f 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: CL 'unthreaded' and 'unCL' and 'un-both-of-them' have the same problems ...
for this and the plenty other questions / bugs regarding circular references / iterative calculations: explanation of the calculation order used in ex$el: https://stackoverflow.com/questions/18084718/calculation-order-when-iterative-calculation-circular-reference-is-enabled checked with ex$el 2010, works that way, is there a similar description for calc? differences: for a recalc - F9 - excel does a round as described above, while calc looks like start at the cell in focus, and stops after one round? (after which definition? re-raeching that cell?) thus some calculations give different results, ex$el does show 'invalid results' (results not converging and being off limt of 'minimum change') after the set number of steps, while calc shows err:523 if not reached the change limit, (try second variant of first example from https://www.spreadsheetweb.com/excel-iterative-calculation/ there are also samples to use iterations for timestamping ...) from C3: '=C4' and C4: '=C3+1' resulting in identical values (after hard recalc with one iteration and minimum change > 1) and results being different by one if you exchange the cells, i'd conclude that calc calculates from bottom to top, from C3 '=D3' and D3: '=C3+1' resulting in identical values (after hard recalc with one iteration and minimum change > 1) and the left cell being one more than the other if you exchange them, i'd conclude that calc calculates from right to left, from C3 '=D2' and D2: '=C3+1' resulting in identical values (after hard recalc with one iteration and minimum change > 1) and D2 being one more than C3 if you move it to B4, i'd conclude that calc first calculates the rightmost column from bottom to top, and then advances left to the next columns, so there are simple but significant differences between the schemes of Ex$el and calc to solve circular references, since the choice of the starting point and the calculation path are important for the output of iterative calculations it is normal that Ex$el and calc give different results, big question: 'do we find our own way right (and if so why 'more right' than Ex$el), or do we want to work more towards Ex$el compatibility? and! who dares to tackle this 'hairy soup'? (cited from erAck)
Dear Rahul Singh, 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