Issue 103156 - Iteration end condition doesn't work properly
Summary: Iteration end condition doesn't work properly
Alias: None
Product: Calc
Classification: Application
Component: ui (show other issues)
Version: OOO310m11
Hardware: PC All
: P3 Normal (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2009-06-27 09:11 UTC by ullizin
Modified: 2013-12-24 13:46 UTC (History)
2 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: 4.1.0-dev
Developer Difficulty: ---

OOo Calc Testfile Iteration (532.27 KB, application/vnd.oasis.opendocument.spreadsheet)
2009-06-27 20:11 UTC, ullizin
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description ullizin 2009-06-27 09:11:53 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.
Comment 1 ullizin 2009-06-27 20:03:34 UTC
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

Comment 2 ullizin 2009-06-27 20:11:00 UTC
Created attachment 63248 [details]
OOo Calc Testfile Iteration
Comment 3 Edwin Sharp 2013-12-24 13:46:23 UTC
Confirmed with
AOO410m1(Build:9750)  -  Rev. 1551264
2013-12-17_04:10:52 - Rev. 1551455
OK with Gnumeric 1.12.6