hello all,
i see a massive problem - massive problems - in LO calc,
this posting is very long, sorry, i’d spend the time to pinpoint to the fault (what i consider being a fault) because i think it’s very critical, and i see that former question have been stopped with silly ‘solutions’ or workarounds,
many many thanks in advance to everybody who tries to understand the problem, does further research or gives any other help. i consider ‘press ctrl-shift-F9’ helpful in diagnosis, but not an option for daily work.
shortcut: >>> the shown value of dependent (referencing, calculating) cells is - in some rare cases - NOT! updated as needed / expected when referenced cells (cells that provide ‘source’ for the calculation) are changed <<<
the problem wasted a lot of my time, gave me headaches for more than 6 weeks now, and has the potential to render the work of years worthless.
i’m at the limit what i can do for / against it, so i kindly ask for help …
example, concrete ‘knock’:
SUM(0 + -747,27) = -747,27
is correct, still showing
SUM(0 + 0) = -747,27
after deleting the second summand is not! correct, at least not when ‘autocalculate’ is on!
reg. its content i can’t provide the original sheet, i’ve attached three screenshots 01_before.pdf, showing the screen with C319 in place, 02_deleted.pdf, showing the same sheet with C319 deleted, and 03_formula.pdf showing the formula in D319.
this posting is not! intended to knock LO calc down, it’s intended to catch this problem and get rid of it,
my talent is limited to be able to see a fault when i meet it, and to narrow down the circumstances,
programming is not my thing, at least not in ‘c’, and the time i can throw in is limited,
there is some hope, i found an old version of LO where some of these errors are gone,
the wrong behaviour shows up in plenty versions - e.g. 6 (.0.7) (x64), 5 (.4.7) (x64), 4 (.4.7.2) (x86) - most of it is ‘gone’ in 4.0.4.2 (x86) and 4.1.6.2 (x86) - first symptoms occur in 4.2.0.1, and it’s still available in the actually newest version 6.2.0.3 (x64)
all tested under win 7 pro x64 SP1 on a Lenovo P70 Xeon E3-1505M v5 with ECC memory,
hurray!!!, the fact that there are! old versions with different - correct? - behaviour confirms to me that
-
there is! a fault, either the old or the new version must be wrong, i suspect the new one,
-
there is hope to find the source of it by comparing the versions,
-
me and my hardware can be considered to be ok,
-
there is a chance to find people to help because i can prove the fault,
let’s start:
the ‘error’ is old, it may be more than one error (one can also have loeces and fleas), the unstable results of changing formatting of a cell from ‘all - standard’ to ‘text’ and vice-versa occurs in the old versions were the other problems are ‘gone’,
the impact is near to making calc ununsable because one can’t trust the results,
i tapped on it when the result of a formula in cell D319 ‘=SUM(B319:C319)’ did not! change when i changed (deleted) the content of C319,
autocalculate was! on, and the shown wrong result was corrected on plenty changes in other areas of the map, e.g. deleting another sheet in the map, but reproduceable D319 was not actualized when i deleted C319. (0 + -747,27 = -747,27 was correct, after deleting -747,27, thus giving C319 a value of zero, i expected 0 + 0 = 0 and got 0 + 0 = -747,27 instead).
the same function with the same formatting worked correct in the row below!
after some - random and rare - occurences of similar miscalculations i started research and googling, that brought up some - rare - complaints on the web, from different useres, different versions, different OS’s, different hardware, all having similar problems:
after changes in cells which are referenced by formulas in other cells, the shown value of the dependent cells is not recalculated as neccessary, and that despite the fact that ‘autocalculate’ was on.
the initiating changes could be deleting a cell (not the cell istself but it’s content), undoing a move of cells, changing the formatting (from ‘all - standard’ to ‘text’), and maybee others …
most of these problems disappear after changes elsewhere in the sheet, most of these problems disappear after pressing ctrl-shift-F9 (‘hard recalc’), that makes it difficult to pinpoint, reproduce, or provide testing material,
thus it’s difficult for people or programmers to help, most of the complaints where refused ‘you must have made a mistake’, ‘is autocalculate on?’, ‘it’s not a bug, it’s a feature’, and fizzle out ‘just press crtl-shift-F9’,
but to have random results which you can correct by a ‘forced hard recalculation’ makes ‘autocalculate’ a bad joke, is like driving a car where steering and brakes work ‘in most cases’, and should only be allowed when the program starts with a big red warning popup:
‘disclaimer!, warning!, this program is free software and comes ‘as is’ without any warranty. it is known that it produces drastic wrong results in some cases, you may not! trust any results of this calculation sheet unless you have checked them by manual recalculation. please confirm that you’d read and understand this warning, the program won’t start before you accept it. programmers know about this bug but refuse any responsibility’
i think it’s a bug in the part of code where the autocalculation is triggered, maybe related to ‘decisions’ how to handle references when the referenced source is moved (when single cells are addressed the formula in the referencing cell is changed on moves of the referenced cell, when ranges are referenced moving cells within them produces funny results), but that’s just my two cents.
references:
https://help.libreoffice.org/Calc/AutoCalculate
states: ‘All cells are recalculated after a sheet cell has been modified. Any charts in the sheet will also be refreshed.’
obviously that’s not the case - or produces wrong results - in:
-
i’d like to start boiling down from this case, the strange behaviour is reproducible, a test file is available, i’ve tested it working correctly 'til version 4.1.6.2, and failing in plenty versions since 4.2.0.1 (i didn’t test the betas). unless this problem isn’t gone nobody can trust any results of LO calc unless he’s pressed ‘ctrl-shift-F9’ and not! altered anything past that moment.
-
starting in ver. 4.2.0.1 the sum in col. ‘T’ isn’t updated correctly when you undo a move of cells from col. ‘E’ to col. ‘G’.
-
one point is common in many reports i’ve read, they complain about strange effects in the update / recalculation of a “sum of a range”.
- autocalculation worked as expected in former versions, autocalculation works in files saved as xls, is broken when saved as *.ods,
https://listarchives.libreoffice.org/de/discuss/msg17636.html
- no recalc after undoing deletion
similar bugs are reported - and some of them ‘solved’? - in e.g.:
- wrong recalculation / wrong refresh, reported, marked ‘fixed’,
https://bugs.documentfoundation.org/show_bug.cgi?id=80846&redirected_from=fdo
- wrong results after an undo,
above post is a hint that (some of) the problem is introduced between version 4.1.6.2 and 4.2.0.0.beta1
https://listarchives.libreoffice.org/de/discuss/msg17636.html
- wrong sum after undo,
https://bugs.documentfoundation.org/show_bug.cgi?id=92749
- wrong lookups in large file till changing recalc options, introduced somewhere after version 4.0,
and now, please, help to kill this out, or find where i’m wrong …
many many thanks in advance to everybody who tries to understand the problem, does further research or gives any other help. i consider ‘press ctrl-shift-F9’ helpful in diagnosis, but not an option for daily work.
thks,
newbie-02