Download it now!
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)
(earliest affected)
3.3.4 release
Hardware: All All
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Calculate 126866
  Show dependency treegraph
Reported: 2012-03-01 12:44 UTC by Rahul Singh
Modified: 2019-12-27 10:50 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:

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

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:
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: (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 ...