Bug 46845 - Iterations not converging in Calc
Summary: Iterations not converging in Calc
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.3.4 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Calculate 126866
  Show dependency treegraph
 
Reported: 2012-03-01 12:44 UTC by Rahul Singh
Modified: 2020-08-02 15:40 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Example spreadsheet with iterations (23.50 KB, application/vnd.ms-excel)
2012-03-01 12:44 UTC, Rahul Singh
Details
previous document, saved as pdf using msExcel 2007 (18.81 KB, application/pdf)
2012-05-29 06:52 UTC, sasha.libreoffice
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Rahul Singh 2012-03-01 12:44:35 UTC
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.
Comment 1 Jose Manuel 2012-04-20 12:56:53 UTC
[Reproducible] with "LibreOffice 3.3.4 - Ubuntu 11.04 (32bit) Spanish UI"
Comment 2 sasha.libreoffice 2012-05-29 06:52:32 UTC
Created attachment 62227 [details]
previous document, saved as pdf using msExcel 2007
Comment 3 sasha.libreoffice 2012-05-29 07:01:36 UTC
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.
Comment 4 QA Administrators 2015-07-18 17:44:38 UTC Comment hidden (obsolete)
Comment 5 Buovjaga 2015-10-23 17:08:34 UTC
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)
Comment 6 QA Administrators 2016-11-08 11:34:28 UTC Comment hidden (obsolete)
Comment 7 b. 2019-12-27 10:50:01 UTC
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 ...
Comment 8 b. 2020-08-02 15:40:39 UTC
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)