Apache OpenOffice (AOO) Bugzilla – Issue 38549
iterative cyclic calculation, error 523, maximum number of steps
Last modified: 2013-02-07 22:36:04 UTC
Iterative cyclic calculation and error 523 is implemented in a way which, although maybe protective towards casual users, gravely restricts the advanced use of Calc spreadsheet. Maximum of 1000 steps is insufficient. I have been using Microsoft spreadsheet to implement numerous Petri net simulations of industrial and financial processes. I am using NON-CONVERGING ITERATIVE CALCULATIONS (formulas' values cycle or oscillate with the progressing calculation steps). Unfortunately, Calc hates me for that and always displays 523 error in the end of the calculation, making Calc useless for this type of modeling. I discovered the deficiency when I tried to propose Open Office Calc as a consortium platform for Petri Net based modeling of cellular pathways here at the Bioinformatics Dept. of Academia Sinica (Taipei). For all the users whose iterative calculations don't converge, I would like to beg you for an option of turning off the convergency guardian. Also, numbers of iteration I would like to use with my sheets climb to millions, so I would like to raise the maximum steps limit. It is really a big problem rendering the application useless for advanced users.
Hi, this Issue is a double to Issue 20986 I think. Frank PS Prio 2 is mutch to high, I've corrected it. *** This issue has been marked as a duplicate of 20986 ***
closed double
Reopening. This is not a dupe of issue 20986. In fact it is a feature request / RFE to increase the possible number of steps in an iteration, and for a possibility to disable the convergence check that produces Err523 results if the iteration does not converge to values with a small epsilon as specified under Tools.Options.Spreadsheet.Calculate. Not having the convergence check error but getting some sort of result instead can be useful in some cases, though results may be somewhat arbitrary then.
Reassigning RFE to User Experience.
Thanks for recognizing the problem. I am using formulas that oscillate or cycle (like switching relays). To take this issue a little bit further, instead of filling the cells with "Err 523" it would be better to fill them with cell-specific and user-defined RESET VALUE. Reset could be invoked on user demand or automatically in case of calculation network crash (overflow). Usufulness of proposed reset feature is not apparent at the first glance, but once you implement a collective dynamic model of a hundred of Czech power plants or a hundred of biological pathways in a spreadsheet, things like system hysteresis and calculation network crashes in case you make a typo or a random mistake become important. (Under MS Excel,) once your calculation network crashes, error values propagate and there is no way to raise your network from dead but to implement reset mechanizm manually in most of the formulas (until you break all the cycles in the connectivity graph), which tends to double file size and calculation time. Besides crashing, a complex calculation network may go astray (to an undesired solution), which also makes built-in RESET useful. But implementing this feature may require adding one more property (RESET VALUE) to the cell, so I should probably submit built-in RESET wish as a new issue.
To grep the issues easier via "requirements" I put the issues currently lying on my owner to the owner "requirements".