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
