Apache OpenOffice (AOO) Bugzilla – Issue 20986
Iterative calculation freezes Calc
Last modified: 2013-08-07 15:15:02 UTC
I am in the process of creating a complex spreadsheet to model the effect of UK government policy to encourage renewable energy development. The incomplete spreadsheet can be downloaded from the given URL. Don't try to make too much sense of it - it's at an early stage and may well be badly flawed as well as incomplete. The policy is recursive (i.e. the amount of renewables expected to be delivered has an effect on the forward price, which determines how much will be built, which again affects the price). Consequently, I need to use iterative calculations. When I create the first formula with an iterative reference (it actually sets up a complex chain of references), several of the cells give a 522 error, as they should with iterations turned off in the settings. However, enabling iterations reliably crashes OpenOffice. It simply freezes, taking up a very large proportion of CPU time. There is no sign of a progress bar (which I assume should show up at the bottom of the window) showing something happening in the background. I have left it a good long time (several minutes), in case it is simply chewing over the calculations without indicating progress. I have tried changing the iteration settings before opening the spreadsheet, to reduce the max number of iterations (as low as 10) and increase the minimum change (as high as 1). None of this helps. As soon as I turn on iterations or open the spreadsheet when they are enabled, OpenOffice freezes. I have saved the spreadsheet in XLS format and opened it in Excel. Excel happily performs the iterative calculations (they bounce wildly, rather than settling, but that is actually in accordance with what one would expect given the policy - I will probably need to add some artificial dampening to get it to move towards a median position). As far as I can tell, this spreadsheet works OK in Excel, so it seems to be a problem with OpenOffice's handling of iterative calculations. Cheers, Bruno
Created attachment 10165 [details] Spreadsheet with iterative calculations that crash OOo 1.1RC3
Hi Eike, it seems to be yours. CPU load is around 99% and memory consumption is jumping up and down about 200 kB. Loop ? Frank
I have now upgraded to Mandrake's OpenOffice.org 1.1-0.rc4-2mdk packages. I understand this is pretty close to 1.1 RC5/final. The problem is still present with this version. Bruno
Bruno, Any chance to narrow down the document to something being more debugable (read: much less data and formulas) and still have the loop reproducible? Otherwise this is going to be a nightmare..
One way or another, I guess this is going to be trial and error. I'll see what I can do, but I'm not sure what's causing the problem, so which bits I can cut out and still retain the behaviour.
Well, the file size isn't much smaller, but I've cut out all but the first four years on the second sheet, and any other redundant rows I could find. I'll attach it as RO_model2.sxc. This is the easy bit. I could probably go a little further and cut another year or two (but I had probably better leave at least two). But narrowing it down beyond that will take rather more work. Does this help, or do I need to go further?
Created attachment 11111 [details] Simplified version of original attachment (RO_model.sxc)
By the way, I checked, and RO_model2 still crashes OOo when iterations are enabled, but calculates fine in Excel.
I never got a crash, but Calc freezes reproducible, adjusting summary.
Created attachment 20182 [details] For the curious: dependency graph of formulas, stripped down to the essentials.
*** Issue 38549 has been marked as a duplicate of this issue. ***
HI EVERYONE, Frank has just told me that my issue (38549) was a duplicate of this one. It's pretty close, but not exactly a duplicate. My humble plea boils down to: (1) please add the option of turning off the convergence guardian (error 523) and (2) please increase the maximum allowed setting for iterations to somwhere around 10E9 (yeah, there are people thinking about spreadsheets on supercomputers - sorry for that, I am a biologist, not a programmer). TO TAKE THIS ISSUE A LITTLE BIT FURTHER, if the convergence guardian is turned off, then it naturally happens to you during the work that whole your complex calculation network crashes (at least in Microsoft Excel) or goes astray and that's why all my formulas have to start with checking if the input cells have sane values or not, which I hate. I PROPOSE THAT besides FORMULA and VALUE, each cell should have one more property, RESET VALUE, which is pasted onto VALUE when your calculation network goes astray (reset invoked upon user command or even automatically). I have to implement reset system this manually in all formulas of my calculation networks and it tends to double the size of my file and slow down the calculation. I think the option to reset certain cells with a given cell-specific user-defined value should be built in the application, but never dared to bug MS about this.
boris_stitnicky, Please, this issue is about a specific problem. Don't mix it with feature requests or requests for enhancement, which are handled differently. I reopened issue 38549 for that reason. Thanks Eike
On branch cws_src680_calcer: sc/inc/cell.hxx 1.16.30.2 sc/inc/document.hxx 1.84.8.1 sc/inc/recursionhelper.hxx 1.1.2.1 sc/source/core/data/cell.cxx 1.25.28.1 sc/source/core/data/documen2.cxx 1.46.8.1 sc/source/core/tool/interpr4.cxx 1.31.52.2
Additional files: sc/source/core/data/cell.cxx 1.25.28.2 sc/source/core/data/documen7.cxx 1.7.222.1 sc/source/core/tool/cellform.cxx 1.2.290.1 Ready for QA. re-open issue and try to reassign to oc@openoffice.org
try to reassign to oc@openoffice.org
try to reset resolution to FIXED
verified in internal build cws_calcer
closed because fix available in OOo1.9m87