While doing some spreadsheet exercises I stumbled upon a strange problem. Every time I try to copy formula containing cells (selecting and dragging) the calculated values are different. I am attaching the file affected by this problem.
Steps to Reproduce:
1. Select cells A4:Q4
2. Drag them all the way down to row 152
3. Repeat 1. and 2. a few times to see how cell values change
4. Reopen the file, should be correct now
Calculated values are wrong and different every time
Calculated should be correct and identical every time.
User Profile Reset: Yes
Closing and opening the file calculates correct values.
Created attachment 150564 [details]
Thank you for reporting the bug.
To be certain the reported issue is not
related to corruption in the user profile, could you please reset your
Libreoffice profile ( https://wiki.documentfoundation.org/UserProfile ) and
And please copy information from Menu "Help/About LibreOffice"
so we can see the exact LibreOffice version you use.
> Actual Results:
> Calculated values are wrong and different every time
Does it work, if you do a hard recalc after dragging?
-> Menu "Data/Calculate/Recalculate Hard (Ctrl+Shift+F9)"
Have you tried with disabled:
Menu "Tools/Options/LibreOffice Calc/Calculate"
CPU Threading settings
[ ] Enable multi-threaded calculation
I have set the bug's status to 'NEEDINFO'.
Please change it back to 'UNCONFIRMED' once the requested info is provided.
I tried reproducing this bug in safe mode and the same thing happens. Libreoffice version I am using is the following: 188.8.131.52, Build ID: 20 (Build:2) (installed from Solus repositories). Pressing Ctrl+Shift+F9 after dragging corrects all the values. Disabling multi-threaded calculation seems to fix this problem.
setting to "NEW" even if i can not reproduce at the moment, but it sounds understandable and seems to be related to/a duplicate of:
Editing: Err:522 on copying of rows with "Calc: threaded" enabled
instant repro with
Version: 184.108.40.206.alpha0+ (x64)
Build ID: 35d9a2618dc0116378ab795a7b9277d248c5afd4
CPU threads: 8; OS: Windows 6.1; UI render: default; VCL: win;
TinderBox: Win-x86_64@42, Branch:master, Time: 2019-04-05_04:55:04
Locale: de-DE (de_DE); UI-Language: en-US
Err:522 in K153:N153
but not! repro with *not threaded*, three attempts, all identical, no err:
win 7 pro x64
os: not 'all', but linux and win
Created attachment 156818 [details]
shrinked sheet with the error still occuring
looks like one more of my well beloved 'shared formula breaks autocalculate' or 'neglected dependencies' or 'threaded still experimental' problems.
starts with cell H5 being calculated to 21 instead of 30 on drag or copy from row 4 to row 5.
fails with 'threaded' or 'CL' enabled on my system, works better with both off,
minimum reproducer as of now:
attached sheet piraci_test2.ods (stripped some rows and columns), copy B4:H4 to B5:H5, value in H5 (21) wrong against formula (=B5+11) == (19+11) == (30),
as shift-ctrl-F9 helps out ... standard 'autocalculate broken with shared formulae'?
assumption: calculation of H5 is done without taking into account that the value of B5 - copied from B4 as value: "10", formula: "=D3", has to have the relative reference in the formula adapted to "=D4" and thus will change it's value too. 10 (value of copied B4) plus 11 (number to add for H5) will give the 21 shown in H5. things like this may occur when implementing non mature parallelizing mechanisms ... :-(
(i admit that even in theory it is difficult to reconcile the splitting of a task (for parallelized processing) and the keeping together of dependencies (to recognize circular references and for proper 'clarification' of shared formulas) ... big praise to the developers that some of it works at all! ...
looks as nobody is taking me serious ... imho it's time for a fix or kill-out of the source of this minefield of bugs instead of plastering only the errors which make it through to users notice ...
*** kiss *** keep it simple and stupid *** avoid use of exotic constructs and functions *** and experimental features like parallellized computing *** and you'll be happy and save time of your life *** but the training of your brain is neglected *** that helps to stay happy too ***
*** Bug 129753 has been marked as a duplicate of this bug. ***
i'd put some 'digging ideas' there:
Created attachment 157481 [details]
124577_piraci_test8_ori, calculation error with threaded or openCL
ok, as @xisco closed the simplified bug #129753 and thus it won't get much attention:
the sheet presented by the OP for this bug is quite complex, too complex to get the error spotted and solved yet, it's hard to dig through it,
i put in a simplified sheet here, it has two samples reduced to the minimum conditions where the error is appearing, and pinpointing the spot where the error starts,
detailed info see into the sheet,
condensed description: 'reproducible miscalculations with either threaded or openCL activated',
imho the bug is important as it will - often unnoticed - affect plenty results for users working with either openCL or 'threaded' calculation, wich is activated by default,
it would be very helpful if a developer can have a short look at the attachement and decide if a reliable spreadsheet could possibly perform better than that,
Created attachment 157959 [details]
another file pinpointing the bug
have fun trying to spot the fault in the attached file,
starting with a complexity of 3 - even very simple - dependent formulas per row and 100 rows with identical formulas below each other
(that's where parallelized computation is activated if it's enabled by either openCL or 'threaded' calculation)
with the rows 'connected' by one (or more) of the calculations,
calc will react with wrong results if you do an 'unchanging edit'
- e.g. copy the formula from another cell in a column - to the second from top cell of the identical (shared) formulas.
calc will inject wrong values in cells dependent on the copied one, copied from the corresponding dependent cells of the source position for the copy.