To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=103156
User ullizin changed the following: What |Old value |New value ================================================================================ Keywords| |NO-SOL -------------------------------------------------------------------------------- Summary|Iteration steps count limi|Iteration end condition do |ted too low |esn't work properly? -------------------------------------------------------------------------------- ------- Additional comments from ulli...@openoffice.org Sat Jun 27 19:03:34 +0000 2009 ------- 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. --------------------------------------------------------------------- Please do not reply to this automatically generated notification from Issue Tracker. Please log onto the website and enter your comments. http://qa.openoffice.org/issue_handling/project_issues.html#notification --------------------------------------------------------------------- To unsubscribe, e-mail: issues-unsubscr...@sc.openoffice.org For additional commands, e-mail: issues-h...@sc.openoffice.org --------------------------------------------------------------------- To unsubscribe, e-mail: allbugs-unsubscr...@openoffice.org For additional commands, e-mail: allbugs-h...@openoffice.org