Description: while checking old bugs and my system i found that the 'funny' behaviour of #100475, cell H79 in file https://bugs.documentfoundation.org/attachment.cgi?id=125821 being 'cut off' from recalculation after an error condition, is still present in ver: Version: 6.4.0.1 (x64) Build ID: 1b6477b31f0334bd8620a96f0aeeb449b587be9f CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: GL; VCL: win; Locale: de-DE (de_DE); UI-Language: en-US Calc: Steps to Reproduce: 1. open linked file, 2. set to err: by switching iterations off, 3. switch iterations on again, 4. observe cell H79 calculated to '0' despite it's a clear formula referencing only one other calculated cell, 5. hard recalc or changing referenced values will bring H79 back to live, but on the first shot the result is wrong Actual Results: H79 '0' after re-enabling iterations, Expected Results: H79 correctly calculated value after re-enabling iterations, Reproducible: Always User Profile Reset: Yes Additional Info: Version: 6.4.0.1 (x64) Build ID: 1b6477b31f0334bd8620a96f0aeeb449b587be9f CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: GL; VCL: win; Locale: de-DE (de_DE); UI-Language: en-US Calc:
Hi b, you don't need to create another issue for bug already reported. Instead, what you can do is: 1. Write a new comment saying: Still reproducible in <version> and the steps you used to reproduce it ( keeping them as simple as possible ) Long comments scare QA/Dev people Closing as a duplicate of 100475 *** This bug has been marked as a duplicate of bug 100475 ***
hello @Xisco: i did - just tried to - think before typing ... as the original complaint of #100475 disappeared in some way (was 'norepro' for me), and what i reported persisted, thus might have a different source, i thought a new report would fit better than appending to a thread where other people look, test as 'norepro', and leave, you are the pro, just handle in a way that it can get resolved ...
2. set to err: by switching iterations off, 3. switch iterations on again, Where should i look... not regular calc user
hi @Telesto, a real minefield there, sure you want to step in? iterations: repeated cycling through loops of 'cycling dependent' formulas which - regarding that dependecies - cant be calculated 'straight forward', but are narrowed towards a desired precision by those iterations. used for complex dependencies (plenty roundtrips, high precision), and by some people also as counter / running total / piggy bank (one roundtrip, low precision), somewhat similar to 'goal seek' and 'solver'? types: converging - solveable, diverging - not solveable, undefined - more than one solution, never been working correctly in calc? switch on: <tools - options - libreoffice calc - calculate - iterations> steps: max roundtrips to perform, minimum change: treshhold to stop iterations once changes are below, border to show 'errr:xxx' if changes are above limit when run out of iterations, one can either try with more iterations, or restart iterating (F9 or ctrl-shift-F9?), first results will be kept and if precision is reached after more attempts results are shown, critical: start values, imho calc tries often with '0' instead what's in the cells when you enable iterations, critical: where in the circle to start the loop, critical: what to do with nested loops, nice to start: 46850, replace top row by '1's, place focus inside formula range, and repeatedly hit F9, do often enough to see '1's converging towards the center, have fun ...
(In reply to b. from comment #4) > but are narrowed towards a desired precision by those iterations. That is the LibreOffice assumption that those calculations are convergent. Actually they are simply cumulative, not always convergent. > somewhat similar to 'goal seek' and 'solver'? A bit more specific, I guess. AFAIK goal seek and solver are based on inverse problems; iteration doesn't try to invert anything for the user, so it has no specific root-finding or algebraic/symbolic manipulation algorithms. > types: converging - solveable, diverging - not solveable, undefined - more > than one solution, I should note that MS Excel never actually cared about that classification; it simply runs what the user has commanded. Except when the variation gets too low (this is not really implemented in Calc). > never been working correctly in calc? As far as I can tell, never. > steps: max roundtrips to perform, The number of steps is not consistently followed in Calc, especially when it hides the accumulated value behind "Err:523". For "converging" numbers the number of actual iterations run seems to be exactly 3 for the first time, and 2 on recalcs, instead of e.g. the default 100. This only changes if the number of iterations is set to just 1. > minimum change: treshhold to stop iterations once changes are below, border That is the original Excel behavior, that Calc DOESN'T follow. > to show 'errr:xxx' if changes are above limit when run out of iterations, Now that's Calc-specific, and prevents lots of uses of the feature (as I wrote in bug 136897). And iterations are still calculated underneath even when the only visible thing is Err:523. Version: 7.3.1.3 (x64) / LibreOffice Community Build ID: a69ca51ded25f3eefd52d7bf9a5fad8c90b87951 CPU threads: 4; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win Locale: en-US (en_US); UI: en-US Calc: threaded
(In reply to b. from comment #4) > one can either try with more iterations, or restart iterating (F9 or > ctrl-shift-F9?), first results will be kept and if precision is reached > after more attempts results are shown, > > critical: start values, imho calc tries often with '0' instead what's in the > cells when you enable iterations, If the cell has its formula changed somewhat, the starting point is 0 (even if it's a string iterated calculation) (which is consistent with MS Excel). If you do Recalc or Recalc hard, the value is not reset, thankfully (also consistent with Excel). > critical: where in the circle to start the loop, MS Excel seems to have a well-defined behavior about that, so it's interesting to consider following it. Usually cell evaluation order is based on a dependency graph/tree, but cells that are directly or indirectly dependent on iterative calculation are calculated in an simpler position-based order. You can check that by putting a simple iterated formula somewhere (e.g. on E7: =E7+1), and then simple references (=E7) around it in both sides. First, those formulas are sorted according to their sheet's name (I mean the end-user-defined name used in formula references, not the hidden VBA name); rename the sheets and watch the difference. Then they are sorted by row, and last by column. E.g. when referencing E7, the value in F6 or D7 is one iteration behind E7's value, but in F7 or D8, it's the same value as E7. As of LO 7.3.1.3 Calc, the behavior is different, apparently relying on a dependency tree even for iteration-dependent values. A more complex experiment would use two circularly dependent cells (e.g. E7: =H7+1; H7: =E7+1). Here again, Excel uses the sorting I just mentioned, so H7 is 1 unit more than E7. In LO Calc the opposite effect seems to occur. > critical: what to do with nested loops, What do you mean? Version: 7.3.1.3 (x64) / LibreOffice Community Build ID: a69ca51ded25f3eefd52d7bf9a5fad8c90b87951 CPU threads: 4; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win Locale: en-US (en_US); UI: en-US Calc: threaded
Created attachment 178686 [details] Excel workbook testing evaluation order in a circular/iterative context, including screenshot of Excel behavior.