Apache OpenOffice (AOO) Bugzilla – Issue 956
Iterations referring the last result of the same formula cell don't converge
Last modified: 2017-05-20 11:13:53 UTC
One problem with the way the options are set up the interative solve is turned on or off for the application not the specific SS. In Excel the document file holds this setting information. This is important because if you don't realize you have iterative solver on you may not catch circular calculations. One other note the iterative solver has a bug. I have an example illustrating if you can point me where to send or up load.
Created attachment 260 [details] Here's the example file that doesn't work properly. Change the marked values on sheet three to reproduce, you'll need to manually turn on iterations
My bug
Apart from the setting not being imported there are other problems preventing the calculation from resulting in the same value as in Excel. The first cell encountered to have a circular reference is taken as "the iterative cell" whose value must change more than the specified epsilon to continue iteration. This leads to a premature break of the iteration if more than one cell should be considered as "iterative cells" instead like it is the case in the attached document. Calculation must continue there until none of the involved cells has a value change greater than the epsilon value. You'll get a similar effect if you press F9 (recalculate) several times after which the desired result shows up.
two issues: 1. the excel import. gunnar had this on his todo list and finshed import of iterations just today. 2. see the comment of eike above.
Support for im-/export of iteration settings will go into 633c
yep, it's mine
assigning to correct account
reaccepted..
changing QA contact from bugs@ to issues@
Hi, appears to be fixed in 643. Well done, thanks!
Target the second part (how the iteration works) to OOo 2.0
Created attachment 4000 [details] Examples provided by Max-Ulrich Farber in http://www.openoffice.org/servlets/ReadMsg?msgId=485733&listName=discuss
Developer notes: later. 2.0 only if time permits.
According to the OpenOffice.org roadmap (http://tools.openoffice.org/releases) this issue was retargeted to OOo Later.
*** Issue 23737 has been marked as a duplicate of this issue. ***
*** Issue 34851 has been marked as a duplicate of this issue. ***
Btw, the Buy_house_example.xls of the first attachment calculates right as of SRC680_m85, where CWS "calcer" was integrated. The examples of Beispiele.zip (second attachment) refer the very same cell in one formula and add some value, which of course never converges, therefor the Err523. This is still an issue compared to the behavior of a competitor's product. Changing summary accordingly.
This behaviour still keeps me from switsing from ms-office. It would be great to finaly find a solution for this problem. I think there should be an option in your tools > options > calc > calculation to select wheter the calculation should stop at: a minimal change of 0,001 x or 0 and a maximum iteration steps of 100 so the user can make his own choice
Created attachment 42321 [details] Testcase iterations
Created a new test-file. Go ahead and give it a try. The sheet is about bending moments and normal forces in a concrete member. To calculate stresses, iterations are made. This is not solvable without iterations, unless you are able to solve 3d power and 4d powered equotations. Notice when you fill in the numbers -700, -500 or -200 in the blue box, excel will calculate the solution for you. Notice that when you try to calculate the sheet with the use of OOo, OOo wil give an error with the default settings. (Be sure to set your calculate-options right, with iteration set, and press recalculate button once or twice) When set to a number of 1000 calculations and an change of 1,0, filling in the same numbers a few times, one will finaly get a good awnser in OOo. Ofcourse this is a long way to go, compared to excel.
*** Issue 75988 has been marked as a duplicate of this issue. ***
For completeness: test case of issue 75988 is: Iteration steps to 1, minimum change to 1. A2: =IF(A1=1;A2+1;A2) Change A1 to 1, A2 should display now 1 instead of 2.
*** Issue 78038 has been marked as a duplicate of this issue. ***
Testcase from issue 78038: If the cell A1 contains 1 and if the cells A2 contains =A1+A2 then every time you press Ctrl + Shift + F9 then it adds 2 to the cell A2 instead of 1. (In the option menu, the box iteration is, of course, checked) In Excel it works as expected : it adds 1.
Reset assigne to the default "issues@openoffice.apache.org".