[Libreoffice-bugs] [Bug 129199] AutoCalculate silent, deceptive fail. Indicative pattern of one edit step lag in AC update noticed.

[bugzilla-daemon at bugs.documentfoundation.org]

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

--- Comment #12 from R. Bingham <rdbingham at verizon.net> ---
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.

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20191214/7c48c999/attachment.htm>