Description:
hello all,
libreoffice calculates 1 plus 1 to be 3, it's not! a matter of rounding,
i couldn't stand waiting if and when someone will fix tdf#123714, thus played and 'stressed' some sheets to narrow down where errors evolve,
i found one effect that i consider more than critical:
reproducible in plenty situations the following happens:
after making a copy of a cell with a formula, and editing that copy, the originating cell is excluded from autocalculate,
no matter what you do with the copy, you may even delete it from the sheet, the 'broken' source cell will always need 'F9' or 'crtl-shift-F9' to be recalculated after changes in the values of the cells referenced in the formula, thus you can see: 1 + 1 = 3 for the sum function '=SUM(A1:B1)' in C1 with '1' in A1 and changing B1 from '2' to '1'.
formula, formatting, everything else is correct, even calculating works correct *when* it's initiated by F9, the only 'fault' is: the cell is excluded from autocalculate!
you can make a new copy of the cell with the 'first formula', and it - the source - will be included in autocalculate again,
until you edit the new copy, then autocalculating for the source cell will be broken again,
(i don't know if it's affecting only distinct formulas and cells, i have the impression formulas calculating ranges fail ('=SUM(Xi:Yj)', '=MAX(A1:A15)') while formulas calculating distinct cells work ('=A1+B2' or similar) just play with it, below is one general description and under 'steps' one failing sample with concrete values)
general:
- check that autocalculate is on,
- put a formula in one cell,
- make a copy of that cell,
- put some data in the sheet to be calculated by he formulas,
- check the results,
- change something in the area / cells used in the formula,
- check that the results change correctly,
- edit something in the copied formula while leaving the cell with the 'source formula' untouched,
- watch that from now on changes in the area / cells used in the formulas were not! anymore reflected in correct results in the 'source-formula-cell', while the 'copied-formula-cell' still calculates correctly,
that's definitively *not* how a reliable spreadsheet should work, changes producing erroneous results in cells not even touched by the change.
if the above description works correct in your case pls. check the
concrete sample in 'steps to reproduce'.
my Version: 5.4.7.2 (x64)
Steps to Reproduce:
1. check that autocalculate is on,
2. put '=SUM(A1:B1)' in C1,
3. copy C1 with 'ctrl-c',
4. insert that copy in C2 with 'ctrl-v',
5. put '1' in A1 and A2 and '2' in B1 and B2,
6. check that C1 and C2 calculate '3',
7. edit C2 from '=SUM(A2:B2)' to '=SUM(A2:B3)', do not! touch C1,
8. change B1 from '2' to '1',
9. check the value of C1, in my case it still shows '3' while calculating 1+1,
10. watch that from now on changes in the area / cells used in the formulas are not! anymore reflected in correct results in the 'source-formula-cell' C1, while the 'copied-formula-cell' C2 still calculates correctly,
Actual Results:
C1 isn't updated on changes of A1 or B1
Expected Results:
C1 should be updated by autocalculate in the same manner as C2
Reproducible: Always
User Profile Reset: No
Additional Info:
maybee that's the source of plenty similar bugs, maybee it can be triggered late after 'construction' of these 'shared formulas' ('landmines' in sheets causing destructions in areas far away?), maybee it's not the real fundamental fault but only an effect in a shell around it ... but, not negotiable, it should be checked and fixed as fast as possible ...
Version: 5.4.7.2 (x64)
Build ID: c838ef25c16710f8838b1faec480ebba495259d0
CPU threads: 8; OS: Windows 6.1; UI render: default;
Locale: de-DE (de_DE); Calc: group
i had to choose an 'earliest affected' version, could only tell about my actual install, i'd bet it's in since 4.2

and to make it work without a hard recalc a save & reload cycle is necessary.
this issue is not reproducible with:
Version: 4.4.7.2
Build-ID: f3153a8b245191196a4b6b9abd1d0da16eead600
Gebietsschema: de_DE

narrowing down:
fails looks dependent on placing the copy above or below the original,
(maybe that's the situation where 'shared formulas' are created)
fails looks dependent on formulas calculating a range,
('=SUM(A1:B1)' and '=MAX(A1:B1)' will fail, while '=A1+B1' and '=SUM(A1;B1)' will work),
there must be some sort of 'property' with a cell that can lock it from being 'autocalculated'?,
or some sort of label attached to its 'place in the sheet' saying 'autocalculate unneccessary'?,
this property is hidden, out of view and access by the user,
it violates the statement what autocalculate should do
('All cells are recalculated after a sheet cell has been modified.')
i'd like to yell 'alarm!!!' in capitals but don't know if that's appropriate in the community,
it's 'nice' that this error disappears after save - load,
other similar problems don't!,
this fault is trapping users and waisting time of programmers and debuggers,
the problem is still present in Version: 6.2.2.0.0+ (x64)
Build ID: 5f9104ef6f42d9d42ce3ec564affcba88889e76c
CPU threads: 8; OS: Windows 6.1; UI render: default; VCL: win;
TinderBox: Win-x86_64@42, Branch:libreoffice-6-2, Time: 2019-02-27_17:10:55
Locale: de-DE (de_DE); UI-Language: en-US
Calc: threaded

the originally filed error was about "cells of a 'shared formula group' being excluded from autocalculate after one of the copies is edited" or similar (on my tests i'd change the last one in a column). that error is still present in ver.:
Version: 6.3.0.0.alpha0+ (x64)
Build ID: fe632c86aa250bb355a59ce6acf4dd75eae7afe0
CPU threads: 8; OS: Windows 6.1; UI render: default; VCL: win;
TinderBox: Win-x86_64@42, Branch:master, Time: 2019-03-09_03:34:13
Locale: de-DE (de_DE); UI-Language: en-US
Calc: threaded
i re-tested and edited the third of four formulas in a column, after that all unchanged copies, above as well as below the edited cell, are excluded from autocalculate, they are calculated correctly by forced recalc, but persistent over that excluded from autocalculate ...
b.

Setting as VERIFIED FIXED based on comment 17 and comment 21. ( @Oliver, @b., thanks for verifying ). If you find any related issue, please report it in a follow-up bug.
@Eike, thanks for fixing this issue!!