reproducible with:

Version: 6.5.0.0.alpha0+ (x64)
Build ID: 00262b08984fb2fb91b760d588851bd47ae4d3ac
CPU threads: 4; OS: Windows 10.0 Build 18363; UI render: default; VCL: win; 
Locale: de-DE (de_DE); UI-Language: en-US
Calc: threaded

Version: 6.3.4.1 (x64)
Build-ID: a21169d87339dfa44546f33d6d159e89881e9d92
CPU-Threads: 4; BS: Windows 10.0; UI-Render: Standard; VCL: win; 
Gebietsschema: de-DE (de_DE); UI-Sprache: de-DE
Calc: 

Version: 6.2.8.2 (x64)
Build-ID: f82ddfca21ebc1e222a662a32b25c0c9d20169ee
CPU-Threads: 4; BS: Windows 10.0; UI-Render: Standard; VCL: win; 
Gebietsschema: de-DE (de_DE); UI-Sprache: de-DE
Calc: 

but *not* reproducible with:

Version: 6.1.6.3 (x64)
Build-ID: 5896ab1714085361c45cf540f76f60673dd96a72
CPU-Threads: 4; BS: Windows 10.0; UI-Render: Standard; 
Gebietsschema: de-DE (de_DE); Calc: 

will try to bisect...

seems to have started with:

https://gerrit.libreoffice.org/plugins/gitiles/core/+/eb678c01d35a485b9f79009c126e296d9e811d36

author Eike Rathke <erack@redhat.com>
Wed Jun 20 20:00:23 2018 +0200
committer Eike Rathke <erack@redhat.com>
Wed Jun 20 23:28:47 2018 +0200
tree f60dbb7113470dd6a63958d436200bf71fb3e68c
parent 00b3007976f60bdf65fbe69e3f4f1613b477a059 [diff]

Prepare for Excel's dirty circular references iteration behaviour

In which either convergence is detected or N iterations are
executed and the result is displayed no matter what. Which would
"do N iteration steps" but never detect non-convergence.

Enable that only with an additional option where the user
specifies s/he doesn't want to be informed of non-convergence
errors.

Change-Id: I3caba0c200e16514e39ea46d7f899afc05a5a2cc
Reviewed-on: https://gerrit.libreoffice.org/56183
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Eike Rathke <erack@redhat.com>
sc/source/core/data/formulacell.cxx[diff]
1 file changed

 /cygdrive/d/sources/bibisect/bibisect-win32-6.2
$ git bisect good 117141afbe10ce8b0f7f8e8907ab53e4357310c7 is the first bad commit
commit 117141afbe10ce8b0f7f8e8907ab53e4357310c7
Author: Norbert Thiebaud <nthiebaud@gmail.com>
Date:   Wed Jun 20 15:18:03 2018 -0700

    source eb678c01d35a485b9f79009c126e296d9e811d36

    source eb678c01d35a485b9f79009c126e296d9e811d36

:040000 040000 40e74904e78bd5122a9c030632749a9251caaecd c9dcb0838300180092fa257b6813b1e9e533e1fe M      instdir

f0830413@LAPTOP-98M8UIU5 /cygdrive/d/sources/bibisect/bibisect-win32-6.2
$ git bisect log
# bad: [32300ab0da959bbff1f09736ff655937319e7e3e] source 5f01fe15eb2661f1f9ce12d1d99dc2a705b462ee
# good: [b0a56ec98b1368cb5e3e531e0b3f69565af91609] source 3a801799536e6870f2fb111b1cc00b9575a35a39
git bisect start 'master' 'oldest'
# bad: [696d8ce3656c1a7018e3647f381d01de8432cb19] source 43125f3b0b18b8d9bac9bd459fa234cb5ff9ddb4
git bisect bad 696d8ce3656c1a7018e3647f381d01de8432cb19
# bad: [1218427eb9ce1e9def4adec61f2385296191725c] source 2bc84658cce1df5050fe788dd0c8a0906a1ca2c3
git bisect bad 1218427eb9ce1e9def4adec61f2385296191725c
# good: [d1c9f332a639fe56942428bb9ebf6bbdaa94d688] source 14b7477f599fd740ddc62fea8acf3d234dc81234
git bisect good d1c9f332a639fe56942428bb9ebf6bbdaa94d688
# bad: [f5ca28a02bfef23ed2a29cfbfb2d06d7ed326c07] source 09d9419bf2072fdab2d7c1d1c6a8dee70b9f0f8a
git bisect bad f5ca28a02bfef23ed2a29cfbfb2d06d7ed326c07
# bad: [98816b892e32b4881e477a19651fafa8158a90be] source 96c43a21da48e9ef50f199b9418e089da42a8ebb
git bisect bad 98816b892e32b4881e477a19651fafa8158a90be
# bad: [34d3f272367bf2dd5386666d3580f3128e489304] source 50c8eb70e6ae68794d0867c86c96f4f1b946e38e
git bisect bad 34d3f272367bf2dd5386666d3580f3128e489304
# good: [6723af0316c2cfbaa6fc346ba056d4ba3444a260] source 433fd79e00ee8fb935482f13ca5b270939aefa99
git bisect good 6723af0316c2cfbaa6fc346ba056d4ba3444a260
# good: [79e634a95f18fac4bee89ed85ee1162a7ed05f78] source 87674a28893520eb8bb528c7e774a7ed926976cb
git bisect good 79e634a95f18fac4bee89ed85ee1162a7ed05f78
# bad: [089bae3c8518cc43de14d1d3aff58ae11a5f3f1a] source e1b922a2b302879747ffb93d3a463f2e4d8eca71
git bisect bad 089bae3c8518cc43de14d1d3aff58ae11a5f3f1a
# bad: [117141afbe10ce8b0f7f8e8907ab53e4357310c7] source eb678c01d35a485b9f79009c126e296d9e811d36
git bisect bad 117141afbe10ce8b0f7f8e8907ab53e4357310c7
# good: [31332eef1497eb8cf867075c844d55fd18f22165] source 93e9a3e2b545c853f53ffe28d2795f55ad29f304
git bisect good 31332eef1497eb8cf867075c844d55fd18f22165
# good: [8f16c3d7c126869294a267212be3cf85b96a7129] source b15244d526c285cd109f7195855409f698d0cdc0
git bisect good 8f16c3d7c126869294a267212be3cf85b96a7129
# good: [722cd9f7ca941e74825c9faf3296aa46c4e8f7e4] source 00b3007976f60bdf65fbe69e3f4f1613b477a059
git bisect good 722cd9f7ca941e74825c9faf3296aa46c4e8f7e4
# first bad commit: [117141afbe10ce8b0f7f8e8907ab53e4357310c7] source eb678c01d35a485b9f79009c126e296d9e811d36

repro in: 

Version: 6.2.8.2 (x64)
Build ID: f82ddfca21ebc1e222a662a32b25c0c9d20169ee
CPU threads: 8; OS: Windows 6.1; UI render: default; VCL: win; 
Locale: de-DE (de_DE); UI-Language: en-US
Calc:

repro in: 

Version: 6.5.0.0.alpha0+ (x64)
Build ID: 9ab43aebad67383057d2cc3f754ce2193fa78b4e
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: 

nicely seen and documented, thumbs up,

Oopsie.  In the example .ods the lowest right "CASE 5" comment should read:

CASE 5 AutoCalc value of N20 is 
incorrect for CASE 5 but IS the
correct value (within rounding) 
for CASE 4.

@R. Bingham

many many many thanks, i think it's a great achievement that you've seen this hidden bug, clearly documented it, and beautifully expressed how nasty such things can mislead users. 

@all

They remain - unfortunately - undiscovered for years, and none of us can estimate how many wrong results led to wrong decisions or constructions, from wrong paychecks up to airplane crashes. 

An example might be bridge constructions, where iterations are necessary as a weight has to be carried, material is required to do that, it increases the weight to be carried by its own weight, and it has to be recalculated how much material in which strength is neccessary to achieve sufficient stability. Errors in such calculations can hardly be detected with a calculator or pencil and paper. 

We've had tragic bridge collapses recently?

For this reason I argue in another matter - 'autocalculate broken by shared formulas' - since a long time for a more reliable processing, unfortunately with little success so far. 

Observations on this problem: 

in earlier versions the error doesn't show up (4.1.6.2 and 5.0.0.1(x64)), 

the error 'corrects' itself if you enter the new value a second time (then the correct intermediate result is available, 

a very very vague guess ... could it possibly be that the task(s) - contrary to the setting 'unthreaded' - is/are distributed over several tasks / threads / cores, and that they - since they know nothing about their dependency - independently come to the erroneous assumption to be done? 

In combination with 'not seeing the necessity of iterations regarding ignoring the dependency of the results' could explain the phenomena observed on the surface. 

sorry for my bad english ... deepl ...

b.

Just to be clear regards:

"the error 'corrects' itself if you enter the new value a second time (then the correct intermediate result is available,"

In the "enter the new value a second time" scenario the error seeming to correct itself with a repeated numeric value entry is a coincidence of displayed cell values, not a true correcting calculation.  For ANY subsequent entry in cell C17, even a non-numeric "foo", the observed behavior is that a one-entry-operation lagging, previously calculated and remembered value is being recalled and displayed in cell N20.  Visually useful for an initial C17 entry of "foo", all dependent cells that ARE in correct synchronized AutoCalculate immediately throw a #VALUE! error.  A subsequent C17 entry of, say, "fum", recalls the one-step-lagging calculated error #VALUE! to cell N20.  For more fun, now start entering valid numeric % entries in C17 (10% < X < 100%).

Regards.

Created attachment 156420 [details]
Bug 129199 example .ods w/ corrections + new examples

Updated example .ods with corrected cell comments, corrected row labels + additional cases series.

A more coherent mental model below. No claim this is the implementation logic.

Excluding behavior interception by macro code, each formula cell having predecessor dependencies  has a FIFO output queue with the front-most slot the cell GUI displayed-value slot. In the context of an Auto Calculate sweep instance, AutoCalc calculates a new cell formula value for cell GUI display if any predecessors have been modified, even if the modification did not ultimately change the value of a predecessor, such as manual re-entry of the same value. AutoCalc then pushes the new display value in the back of the FIFO queue.

The “standard” AutoCalc behavior is that all such FIFO output queues are trivially single-slot such that a push of a new value displaces the prior value from the front-most cell GUI display-value slot. Thus all display-value slots are logically coherent for a given AutoCalc sweep instance.

As demonstrated in the example .ods, the formula cell N20 behaves as if it has a TWO-SLOT output FIFO behaving as:

A) (CASE 1) Initialize the example .ods per its embedded instruction. N20 displays 602.21 which is correct for the given predecessor values.

B) (CASE 2) Manually change cell C20 to 70%. Cell N20 display does NOT change from 602.21 to the expected value of 526.89.

C) Manually enter ANY value, even an error inducing value such as “foo”, in either of the input cells D19 or C20. Cell N20 now displays 526.89.

The two-slot FIFO queue interpretation is:

Step A using RECALCULATE HARD (or equivalent at file open time) initialized the N20 output queue to a display-slot value of 602.21 and an empty queue slot behind the display-slot.

Step B AutoCalc DID calculate a new value and pushed it in to the N20 queue, but since there was at least one empty slot behind the display-value slot in the queue, the existing display value was not displaced. Predecessor cell value displays and N20 display are no longer logically coherent.

Step C AutoCalc DID calculate a new value and pushed it in to the N20 queue, but unlike Step B, the queue was already full, thus the existing display value was displaced by the value from the Step B AutoCalc sweep instance sitting in the queue, not the newly pushed value from the current Step C sweep instance.

Note that what matters for N20 display after Step C is that an AutoCalc sweep was triggered that visited N20 and pushed it output queue along by one. What was entered to trigger the sweep is logically irrelevant to the value display by N20 for a given sweep but you can still have a deceptive coincidence of predecessor entry value and N20 display that appears logically coherent.

Note that this two-slot queue model appears to work even when AutoCalc posts a “#VALUE!” cell-in-error condition for N20. The display of “#VALUE!” is delayed by one AutoCalc sweep.

The apparent ability of RECALCULATE HARD to force logical coherence suggests that it could be offered as a temporary, user-options checkbox substitute for the existing AutoCalculate until root cause identification and remediation is achieved.

Regards.

i just noticed:

attached example spreadsheet has "Iterative References" enabled:

Menu Tools/Options.../LibreOffice Calc/Calculate:
[X] Iterations
    [X] Steps 100
    [X] Minimum change 0,001

Created attachment 156423 [details]
simpler_sheet_failing

hello R. Bingham, 

this bug ... remains me of a child play, one of a group is sent out, the others define the rules, the one is called in again and has to ask questions to find out the rules,  for each question he's given an answer app. for the previous! question, 

thus LO is kidding with us? 

your second file has a shortcoming?, you are already confused about the behaviour? or is it intentional that different solutions are correct? in row 96 you name results correct which are different from row 20 and 27. 

while not being able to solve the mystery logic- or technically, i found a meta-level to explain: in the patch suspected by Oliver Brinzing erAck announced: 'the results are somewhat... funny... '

https://gerrit.libreoffice.org/plugins/gitiles/core/+/eb678c01d35a485b9f79009c126e296d9e811d36/sc/source/core/data/formulacell.cxx

thus we get what he said ... ???

or, as Xerox said to David Kriesel: 'rtfm, it is written there, your fault' 

https://www.youtube.com/watch?v=7FeqF1-Z1g0, very good but in german. 

but ... may be ... your problem boils down to a simpler solution ... 

playing with attached sheet - which is a stripped down version of yours - (key in values in A1), i see and estimate the following: 

- there is! circular dependency, if you c/p the line into a new sheet you get err:522, oopsie: that applies only to specific versions? 

assumption: it's not 'seen' by calc in the original and my derivation because we / you started with too complex formula construction, 

- the formula is 'solveable' in some steps, keying in the same value again, likely it's needed more than two times, G1 is correct in every step, B1, C1 and F1 narrow to the final result in every step, 

assumption: G1 only 'looks' correct, in real it's a sum of two deviating values whose deviations cancel each other out, 

assumption: in your sheet some 'errors' are covered by the roundup and rounddown functions, 

assumption: calc doesn't do any recursion by itself, that way N20 in your sheet is lagging behind, 

- if you switch off 'iterations' you get err:522, 

assumption: calc doesn't do recursion 'in the sheet', only for display? or only to check if err:522 is necessary? 

and now? ask the experts: 

- do we really have recursive / iterative calculation in the sheet? how to activate? 

- if not ... what's the iterative setting in options good for? 

- and your sheet ... why didn't calc fire err:522? 

hope it helps ... reg. 



b.

@R. Bingham

for those short in time: i think it's a problem of a faulty order in processing the circular dependencies, you can interfere / override that by moving N20 in a row above 20, ctrl-X - ctrl-V, but the bug should be searched and killed anyway ... 

long version: 

solved? no :-( 

but hope i can pinpoint the source! 

(call me crazy ... )

sorry for long, who doesn't like shouldn't read, 

after some play around, testing older versions, trying to decrypt the circles and dependencies in your sheet, even installing AOO 4.17 ... 

i came to one very simple test: 

copy cell N20, not the cell but the text inside, 'the formula', 

paste that in e.g N18, 

in the same way copy P20 to P18, and change it's formula to referencing N18 instead of N20, 

repeat the tests and cases you studied before. 

on my system: N18 and P18 working correctly, N20 and P20 failing every second case. 

that leads to the idea that your bug might be a flavour of the well beloved (only by me?) old! 'autocalculate broken' bug, 

... one could have seen that earlier from ctrl-shift-F9 working ... :-( ... 

new insights from this bug: 

- the error / bug evolves from a special constellation between dependencies in formulae, and position of cells in the sheet, see experiments below, 

- in this case the exclusion is not 'total', just 'lagging', 

- but it depends in any way on a special cell, it's description in the file? it's position in the sheet? former connections to other cells? 

- your dependencies and circles are quite complex, they need 2 iterations to solve, as arranged in your sheet and worked through by the program they need 3, 

(can be tested by setting down the iterations in the config till you get err:523 on new input) 

i see one more very special aspect for your file: 

the error occurs once two cells (who themselves are referenced elsewhere) reference the same cell (in this case N20 is referenced by M20 and P20) but only! - now it's getting funny - if the referenced cell is in the same row or below! 

going on to play ... 

take the file as is, cut out N20 - ctrl-X - place it somewhere above row 20 (references should update automatically), and you're done. 

what happened? you re-arranged the erroneous order of processing which some programmer implemented earlier, now the circles are solveable in two iterations, and the program works correct. 

try it, 

that is not!!! the solution of the bug in the program! - belive me there is one - and not!!! a suggestion not to fix it! imho it's very important to get things like this out, and to FIX it, really find the evil and correct that, NOT to plaster a 'patch' over this special new occurence ... 

reg. 



b.

On the lead from commentator “b.” I did a manual circularity analysis and this example has 5 (!!) circularities in the calculation of the lagging output cell N20.  Turning off Options->Calc->Calculate->Iterations does indeed result in an Err:52 report in cells. 

Despite the circular references (CRs), I argue there are still bug issues here, just not only the one what I started with and which need there own issues opened for them.

A) The CRs in the example were definitely pilot error. I dislike them in principal as, as also noted in the author comments of the 20 June 2018 code commit https://gerrit.libreoffice.org/plugins/gitiles/core/+/eb678c01d35a485b9f79009c126e296d9e811d36
the solution algorithm available in multiple spreadsheet applications including LO is some version of iteration that is not guaranteed to converge. Somehow I was expecting a warning or error if I created circular reference but see below for LO documentation complaints. Luckily, the original spreadsheet from which the example was drawn could be acceptably re-formulated to avoid CRs.

B) Worse, some CR implementations they do not seem robust against pathological application behavior and in the LO case issue no warnings about non-convergence. Consider the Goal Seek tool as a better user model – it too may not converge or may go pathologically off the end of the real number line but at least you get warning with a commit or don’t commit user choice.

C) In the example .ods I submitted, the LO iterative solution method for the CRs involved DID deliver the correct numerical result but that the result was delivered one calculation sweep out of synchronization with all the other displayed results (semantically incoherent). So still a bug.

D) LO GUI and documentation issues - The documentation at https://help.libreoffice.org/6.3/en-US/text/shared/optionen/01060500.html?DbPAR=SHARED#bm_id3149399 and the GUI Options panel  Tools→Options→LibreOffice Calc→Calculate both use the term “Iterative Reference” which suggests a stepwise calculation of a cell address because everywhere else in the documentation ‘reference’ is used to mean a cell or area address construct.  Stepwise calculation of a cell address is logically NOT what is occurring. What IS logically occurring is a stepwise calculation of cell *values*. The structural formula issue, CRs, has been conflated with the provided CR solution method, the Iterative Algorithm (IA).

“Circular reference” is the term of art used in spreadsheet contexts since the days of VisiCalc in 1979 but for some reason LO has abandoned this user-community historical understanding.  CRs are created by a user deliberately or inadvertently and unless rejected by the spreadsheet application at formula commit time (similar to syntax errors), exist independently of whether the cells involved shows an error state or not, and whether or not some solution algorithm may be attempted. I suggest the phrases “Circular Reference” and “CR Solver” (a naming riff on the existing Goal Seek and Solver tools) separating these concepts should be used in the documentation and GUI. That “CR Solver” currently uses iteration is only of interest to advanced users in fiddling the IA control parameters. Separating these concepts also future-proofs against the possibility of choices of different CR Solvers.

E) IF LO Calc is going to offer CRs, then given how silently risky the solver implementation to date has been, I argue that users need to be able visibly manage them as coherent objects. The Trace Error tool is of little value in locating CRs if the no cell-in-error is shown. Handily, LO Calc has an existing GUI element explicitly designed for meta-data lists: Navigator. Consider a high-level meta-data category of Circular Reference Chains (CRC) that expands to a list of identified CR’s with some naming convention each in turn expandable to a list of cells. A user then has an entre in to what chains exist as coherent pseudo-objects and their member cells, to be understood and managed for solver risks. Such a Navigator CRC pseudo-object could even report the most recent IA sweep convergence result.  Maybe when a CRC is selected in Navigator the currently visible cell members are high-lighted in some way. More broadly, consider a meta-data list of ALL cells in error.

Regards.

of course there is! a bug, and i didn't want to lead away from that, 

imho - pls. recheck - it's not the first report about it, 

https://bugs.documentfoundation.org/show_bug.cgi?id=100475

has very similar behaviour, 

acc. to @Kohei Yoshida's comment: 

https://bugs.documentfoundation.org/show_bug.cgi?id=33330#c5

it's a very old problem ... 

personally i'd like to get some of these old problems eradicated (comment-slowdowns, autocalculate-hangs, shared formulas, circularity / iteration problems ...) because they undermine the trustworthiness of the program, but they are complex, it's difficult code and hard work,  ... 

reg. 

b.

as this bug kicked in with ver. 6.2 (tested 6.2.7.1 failing, thus change 'first affected'), while some better behaviour for #81757 with somewhat similar description newly appeared in 6.2 versions, i assume them being 'related', 

unfortunately we have both problems active in 6.4 versions :-( ... thus it's not a simple toggle in recalc ordering? 

reg. 

b.

Increasing priority to get some attention (at minmum dev comment): reproducible + deceptive fail + regression. Calc results need to be trustworthy.

This bug needs re-titling to reflect comment#9

Repro on Linux with recent master build, so changing to affecting all OSs.

Version: 24.2.0.0.alpha0+ (X86_64) / LibreOffice Community
Build ID: 24d0a62bd75b9a895c419aa165da648ab18f134d
CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-AU (en_AU.UTF-8); UI: en-US
Calc: threaded

Not reproduced in:

Version: 6.1.0.3
Build ID: efb621ed25068d70781dc026f7e9c5187a4decd1
CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk2; 
Locale: en-AU (en_AU.UTF-8); Calc: group threaded

I checked with the linux-64-6.2 bibisect repo that it did indeed start at eb678c01d35a485b9f79009c126e296d9e811d36

Eike, what do you think?

Nothing. Someone else can get their hands dirty with that. If it was me, we'd completely rip out that iterative "feature" and forget about Excel compatibility there (which works only partly anyway) and instead actually solve the circular dependency epsilon calculation and if there is no convergence set an error to all cells involved. Users don't want to see that though and at the same time expect the Excel behaviour (even if that does _not_ resolve/converge in iterations), which we can't provide unless someone completely rewrites that part.

Older releases without https://git.libreoffice.org/core/+/eb678c01d35a485b9f79009c126e296d9e811d36%5E%21/ seems to fix this problem here, the first sample calculates as expected. Whether reverting that commit would do no harm (in the sense of breaking other now working calculations again) I didn't investigate.

But apparently without it doesn't properly either, with LO 6.1.6.3 the attached second sample still needs to be recalculated several times to actually converge.