[Libreoffice-bugs] [Bug 114482] Circular reference accumulator, err:523 or miscalculation

[bugzilla-daemon at bugs.documentfoundation.org]

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

--- Comment #17 from b. <newbie-02 at gmx.de> ---
sorry, long ... 

@carlo: i highly appreciate the work you invest to bring LO forward, 

reg. iterations a 'dummies' sight: 

recursive dependencies / formulas can have 'a solution', 'no solution', 'plenty
solutions' - in real life. 

calc isn't aware of 'plenty solutions', it tries to compute one 'converged'
'narrowed' result, and throws err: if it cannot find one in the limits given by
the settings. 

A1: '=A1+1' has no solution in real life, but has one in calc with 'iterations'
enabled. 

it has more than one depending on the start value and the settings for
iterations. 

it's something like a 'state machine' with the result depending on the state
before the calculation and the 'new round of calculation'. users use this
'feature' to do simulations as a.burger in the initial post. 

one can call it a misuse of a functionality. but the formula A1: '=A1+1' can be
used as a counter or similar ... 

e.g. for the laplace calculation @jean-philippe.grivet did in
https://bugs.documentfoundation.org/show_bug.cgi?id=46850, adding a cell as a
counter could show how many iterations are needed to come out of the err:
conditions, and how many to get a stable result ... but for uses like this a
function needs to be 'understandable to simple minded users' ... and that might
also help to get 'pros' and developers to gain understandable results for their
complex tasks. 

B1: '=B1+A1' has the same problem, there isn't one distinct solution, but
infinite depending on start values and settings for iterations, calc in some
way is neglecting start values ... :-(

the combination A1:'=C1', B1:'=A1+10', C1:'=B1-10' does have plenty - infinite
- solutions in real life, calc doesn't check that but produces one distinct
result (with iterations enabled). one can play with different values entered in
A1 and then replacing that with '=C1', i don't know if it's useful for any
purpose, but it's nice to see how calc and the iteration settings work. 

the combination A1:'=C1', B1:'=A1*1,1', C1:'=B1/1,11' does have only one
solutions in real life, 0 | 0 | 0 , calc tries to come narrow to that and needs
different time reg. the value you start with (A1: '10' -> A1: '=C1'), it takes
some 10.000 rounds to come near to '0', different for different setting of cell
format, if you work with scientific format you can count about +100k iterations
to come below an error of 1^e-307 (smallest value setable) and need more than
one recalc for that as calc doesn't accept values above 32767 for iterations. i
don't know if it's useful for any purpose, but it's nice to see how calc and
the iteration settings work. 

i do not propagate using things that way, but they may help to clarify how calc
works, and thus understand the complaints made in many bug reports reg.
recursions / iterations 'not work' or 'wrong results'. 

i'd like any 'understandable behaviour', especially one that's in harmony with
the documantation. 

- thus doing two additions on the intial insert of a formula and one - acc. to
iterations set to 1 - on every recalc is ... a bug? at least it's something to
mention and be aware of. 

(there had been something in the code with 'performing the calculation two
times per iteration', that's out, i assume it's still in for the initial
round.) 

- as well as not starting with the previous value of a cell when inserting a
recursive or self referencing formula ... it works different if a formula
references itself directly, or via a circle. 

- from playing with formulas like A: '=A+B', and B: '=A+B' somewhere in the
sheet i estimate that the final decision about the order of calculation - if no
other things e.g. dependencies apply one - is done from right to left and then
bottom up, and not! 'left to right and then top down' or 'top down then left
right'. that's contrary to the workflow / thought structure of plenty (most?
all?) users - in occidental cultures! - maybe we see here how the thought
structure of an oriental programmer is traced to the surface ... that's
fascinating, and could eventually explain that we have more complaints from
occidental users ... 

- if allowed calc treats two rounds of calculation before stop and see
difference is within limits, to limit to one circle you have to set iterations
to '1', despite that setting on initial placing of formula two rounds will be
performed ... thats - mostly 

- not a problem for users seeking a solution for a converging formula, 

- but for others who misuse the feature to implement counters, piggy banks or
similar, 

reg. 

b.

-- 
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/20191227/9a8ffdd3/attachment.htm>