Apache OpenOffice (AOO) Bugzilla – Issue 103156
Iteration end condition doesn't work properly
Last modified: 2013-12-24 13:46:23 UTC
The summary of issue 82728 is "Iteration count not saved." As far as I can see, this is not true. But the setting is limited to 1000, which is too low for my application, as it is for oldetc in issue 82728. In Excel the limit is 2^15 - 1 = 32767. With a setting of 10000 my application works fine. In OO.o Calc - with the limit of 1000 - I get an Err:523, unless I luckily find a starting value, that is very close to the correct result. This happens, although I set the change limit to 0.1, which is not an acceptable precision.
OK, the step count is not the real problem. I tested the iteration by stepping through with manual input of the approprate values, and I found that about 30 steps are sufficient. The problem is that, although after about 25 steps the values are changing by less than 1E-006, OOo Calc will produce an Err:523, when I set the "Minimum Change" setting to less than 0.1 . If I set it to 0.1, iteration will work only with a very good starting value, and the precision of the result will be poor - according to the 0.1 setting. I will add the attachment "investment_Templeton.ods" that shows the real application sheet "T. Growth Fund, Inc. (147)" and a strongly simplified sheet "test iter". "test iter" shows a few amounts paid in column C, the sum of these payments in columns D and the sum including interest in column E. Values in column E are computed using the interest rate from cell E3 (indirectly using E4 and E5). In column H there are given market values of the investment (only one in the test sheet). The task is, to find out an interest rate E3 that results in a "sum with interest" that equals the market value. In earlier times of the development of the sheet I really did this manually. In order to automate this task the ratio "Market Value"/"Sum with interest" is computed in column I. In the real application the whole computation is executed repeatedly for each line. In order to do this I have to select the line, for which the iterest shall be computed. (In the sheet "T. Growth Fund, Inc. (147)" this is the first line with an asterisk in column R.) In the test sheet the one existing ratio value I14 is "selected" to I3. From there the "Iteration Formula" J3 computes a new value for the interest rate (by using the difference between the ratio and 100%). In order to compute the desired interest value, a starting value is input manually into cell E3. After that E3 ist set to "=J3", which closes the iteration loop. (With this indirect method you got a chance to input a new starting value into E3 after the whole table has been filled with "Err:523". Then you can set E3 to "=J3" again, without having to retype the slightly more complex formula contained by J3.) In order to analyze the Err:523 problem I stepped manually through the iteration in "test iter". I started with 0 in E3. Then I subsequently typed the values resulting in J3 into E3. The resulting values are shown in "test iter" under the loop table - together with their relative changes from step to step. As you can see this iteration coverges quite quickly, and changes are less than 1 E-006 after step 27. However OOo Calc always produces an Err:523, if I set the minimum change value (German: "Minimaler Änderungswert") (should be called "maximum", because this is an UPPER limit - at this point MS is a bit more correct) to a value less than 1 E-001.
Created attachment 63248 [details] OOo Calc Testfile Iteration
Confirmed with AOO410m1(Build:9750) - Rev. 1551264 2013-12-17_04:10:52 - Rev. 1551455 OK with Gnumeric 1.12.6 Debian