Autocalc not working as expected (2nd-order formulas)

[voxorg]

Calc version 5.4.3.2 (x64).

Sheet is created brand new in this version of Calc. Doesn’t matter if it’s an unsaved Untitled-1 or has been saved - same behavior either way.

There is only one sheet. There are columns of raw data, and columns of formulas that use data (1st-level formulas). Then there are additional columns of raw data, and columns of formulas that use the results of the 1st-level formulas and the additional raw data.

To make this a little clearer as an example:
A1 = 1
A2 = 1
A3 = sum(a1:a2)
A4 = 1
A5 = sum(a3:a4)

The sheet is using min,max,abs, and sum in various ways.

If A1 or A2 is changed, A3 automatically updates.
If A1 or A2 or A4 is changed, A5 is not automatically updating - this is my problem.

Autocalc is on. F9 doesn’t update A5, even though it’s only a single sheet. Control-Shift-F9 works, or copy+pasteover.

So, help?

[newbie-02]

hi,

sorry for late, i’m just collecting announcements about this bug, just to see and clarify how widespread it is …

i know this answer doesn’t help to solve the problem, just as a hint to users: afaik the bug does not! affect the correctness of calculations, it’s ‘only’ blocking the function of autocalculate for some cells, therefore you can trust the displayed results only after a ‘hard recalc’ (ctrl-shift-F9),

as of now: use versions 6.1.7.2 or 6.2.8.2, ‘unthreaded’ and without ‘CL’, do a ‘hard recalc’ (ctrl-shift-F9) before! using any results of a sheet for critical purposes, or use version 4.1.6.2, afaik it’s free from this bug.

you have described a mistake that i have to deal with quite accurately and abstractly, and you mentioned the ‘2-nd order effects’, thanks, unfortunately i didn’t find your report earlier,

i - assume - that the misbehaviour on your sheet happens in columns with similar formulas, or in formulas pulled out of such a column,

calc definitely has a problem with ‘shared formulas’, breaking the functionality of ‘autocalculate’,

as it’s occuring only in rare random cases, and the behaviour changes on different influences (e.g. save and reload, ‘threaded calculation’ or ‘CL’ on or off, lin / win, 32 / 64 bit, often ‘happening but not reproducible’), it’s hard to pinpoint and difficult to debug,

(afaik) it’s introduced around the change from 4.1 to 4.2, with a special handling of identical formulas - ‘shared formulas’ - in cells underneath each other (target was performance),

plenty of it’s symptoms have been worked on in plenty bugs and reports, and many things are better now but - imho - the root cause is not yet eradicated:-(

(it’s quite very! complex to implement such special handling, and a questionable bug-handling to correct only those facets of faults which came to users attention)

reg.

b.

[m-matt]

bug still present on v 7.0.2.2 (flatpak running on ubuntu)
It is easy to generate the bug and I have seen it in multiple sheets over the last few versions of LibreCalc. The way to easily make the bug is create a formula. Then ctrl-D on the cell below. the copied formula will not update when a variable is changed. The original formula will however auto-recalculate fine.

[erAck]

5.4 is quite old. Meanwhile recalculation bugs were fixed, specifically when involving shared formulas (similar formulas in subsequent rows of a column). Upgrade to a recent 6.2 or 6.3 release. Seriously.

[newbie-02]

fixed? … seriously? … not! … look at this:

CALC: Auto recalc does not work.

I use my old ods file years ago. The formulas recalc does not work a few weeks ago. If I press F2 to edit the formula and at the end of the formula I press a SPACE then ENTER it works. One example: C2 D2 3000 =C2/423 When I edit C2 and press ENTER, D2 cell shows wrong value. But if I edit D2 and I press SPACE (so I modify the D2 VALUE ) Calc can calculate the good value. F9 works but I think I should not have to use F9 all the time.

the problem mentioned there in the ‘answer’ from Budi B. L. from Feb. 26 '15 is still broken in all actual versions

the following simple test fails in all versions since 4.2 which i tested:

A1: 2

B1: 3

C1: =SUM(A1:B1)

A1:C1 copy

A1:C10 paste

B:B insert column before

B3:B8 delete cells, shift left

D:D insert column before

C2 delete content

E2 not updated, still showing ‘5’

you may delete the content of all other cells, Ex (x:1;2;9;10) will still show ‘5’ as the result of ‘=SUM(Ax:Cx)’ with Ax:Cx being empty!

both problems ar quite similar to this question.

b.

[erAck]

Did you (or anyone else) report this in the bug tracker with specific instructions to reproduce? If yes, which bug number is it?

Btw, I don’t see how the mentioned problem in the question you linked could be reproduced with that simple example there.

[erAck]

Anyway, most recalculation bugs have been fixed since 5.4

[newbie-02]

bugs? yes:

https://bugs.documentfoundation.org/show_bug.cgi?id=111428#c21

you’ll likely find some more unfixed regarding similar problems there,

i’d write to this question because i like that the poster especially mentioned the ‘2-nd order’ effect, just extrapolate to 3-rd, 4-th, 5-th order and so on … from that i came to:

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