Description: short try: load file from next comment, delete value in B10, observe C10, D8:D12 and E6:E14 changing. copy row 20 and paste over rows 1:20 - it's the same content as before!, delete value in B10, observe only C10 reacting to it, while D8:D12 and E6:E14 show wrong - unchanged - values. hard recalc (shift-ctrl-F9) corrects the view, but it is not sustainable, not thorough, when changing other cells in column B, again the cells in D and E are excluded from refresh long story: i think the problem is 'related' to plenty bugs and reports regarding shared formulae blocking autocalculate, e.g. https://bugs.documentfoundation.org/show_bug.cgi?id=123736, i was asked there to file a new bug, on checking old fault descriptions i stumbled on https://ask.libreoffice.org/en/question/12165/calc-auto-recalc-does-not-work/, the procedure mentioned there by Budi B L: "Still having similar problem Version: 4.4.0.3. This is my case: B10= number, data for these formula C10= formula: =IF(IFERROR(B10,0),1,0) D10= formula: =SUM(C8:C12) E10= formula: =SUM(D8:D12) And those cells copied to B1:B20 The result: ONLY the col C that changed by input or edit the cell in col B. NO changes in col D and com E, related option already been set." fails in ver 6.4.0.0.alpha1+ for linux, x64, build id ends with 3d60d7. it does not! fail loading a saved file with the formulae, but it does! fail if you create the sheet from scratch, or take one row in that sheet and copy it to a range of rows, e.g. 1:20 the following versions failed too (for win german, lo englisch, german locale, i had to change the delimiter in the formula from ',' to ';': Version: 6.2.8.2 (x64) Build ID: f82ddfca21ebc1e222a662a32b25c0c9d20169ee CPU threads: 8; OS: Windows 6.1; UI render: default; VCL: win; Locale: de-DE (de_DE); UI-Language: en-US Calc: Version: 6.4.0.0.alpha1 (x64) Build ID: cc57df8f942f239d29cb575ea5a7cb01405db787 CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default; VCL: win; Locale: de-DE (de_DE); UI-Language: en-US Calc: Version: 6.4.0.0.alpha1+ (x64) Build ID: d456ca6efb98d707427228e8131dfe8a5f27e2f6 CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default; VCL: win; Locale: de-DE (de_DE); UI-Language: en-US Calc: have fun in debug and coding, thanks for any help to get finally rid of this problem Steps to Reproduce: see above description and 'script' by Budi B L Actual Results: under special circumstances some cells are excluded from automatic recalculation, Expected Results: recalc - autocalculate should work for all! cells, Reproducible: Always User Profile Reset: No Additional Info: see above,
Created attachment 156057 [details] 12165_calc-auto-recalc-does-not-work_6_4_0_0_a1_lin64_threaded see what to do / what to check in initial bug description
a fundamental thought ... sorry, I write long, if errors remain in the program for years one may write long to work through them clearly. I think we can continue playing ping-pong for years, 'hurra we fixed the bug' - 'no, there's still something left' - 'we fixed it too' - 'really everything? ... no' ... and so on. That has three disadvantages: - it takes an inordinate amount of time, - it's badly paid, - the program never or only very late comes to a reliable state, I don't think neither me nor other users can be the yardstick for the correctness of a spreadsheet. The complexity we are able to think and control is far below what is possible in the program. So there will still be errors that just haven't been discovered yet, and rocket trajectories will be calculated wrong again and again :-( three possibilities that would be better?.: mathematical check of the code ... very complex ... extensive highly complex test sheets including automated input, saving, reloading, changes etc. whose results are checked automatically, possibly easiest: testsheets and scripts that perform all possible calculations and operations in different versions! of LO and! competing products in parallel, and automated comparison of the results ... has anyone ever thought about this? reg. b.
looks like a not handeled 'edge case' ... simplest failing sheet: ' simple test for edge case fail A2: '1', B2: '=SUM(A1:A2)' copy A2:B2 to A1:B1 observe: #REF! in B1 - correct, cell above A1 is not defined, '1' in B2 - wrong, sum(A1:A2) should be 2, changes in A1:A2 don't affect the result in B2, '=(A1:A2)' fails similar but slightly different, but it's results are funny anyhow, '=A1+A2' works better, '=PRODUCT(A1:A2)' fails on changes of A1 or A2, you need higher values than 1 in A1 or A2 to show it, A1: '2', A2: '4', B2: '=A1*A2', copy A2:B2, paste in A1:B1 fails in updating B2 at copying and subsequent changes in A1, assumption: ranges fail, single references handeled correctly, does not hold, from a little playing around: crossing the top or bottom border with the referenced area of a copied 'range-formula' blocks autocalculate for the same formula in the cell above or below the 'border crossing cell', crossing the left or right border with similar 'transponsed' groups does'nt harm? to check: effects when other circumstances invalidate values in referenced ranges or result of a formula in a shared group. once it's ok in simple calculations ... how does it work cascaded as Budi B L's sample? above tests performed with 6.2.8.2 win(x64), short look into the future: Version: 6.5.0.0.alpha0+ (x64) Build ID: 0c4736a3e6b6e641579f517cf134ce2fd89fe132 CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default; VCL: win; Locale: de-DE (de_DE); UI-Language: en-US Calc: fails too (too hard? apes in writing a bible: you need plenty of apes, plenty of typewriters, and much time to check the results ... there is a small statistical chance to finish before universe ends ... changed concept: add some mandarins checking the results, triaging in "your's i can read so far, go ahead" and "unreadable - start over" will save time and enhance efficiency, may be there are better concepts, mankind proves success is! possible, unproven if by chance, luck or knowledge.) reg. b.
(In reply to b. from comment #3) > looks like a not handeled 'edge case' ... > > simplest failing sheet: > > ' simple test for edge case fail > A2: '1', > B2: '=SUM(A1:A2)' > copy A2:B2 to A1:B1 > > observe: > > #REF! in B1 - correct, cell above A1 is not defined, > > '1' in B2 - wrong, sum(A1:A2) should be 2, Reproduced in Version: 7.0.0.0.alpha0+ Build ID: 28d844a589e52abfe62dc66b888e78665221ba28 CPU threads: 4; OS: Linux 4.19; UI render: default; VCL: gtk3; Locale: en-US (en_US.UTF-8); UI-Language: en-US Calc: threaded
Not reproducible in LibreOffice 3.3.0 OOO330m19 (Build:6) tag libreoffice-3.3.0.4
Also fine in Version 4.1.0.0.alpha0+ (Build ID: efca6f15609322f62a35619619a6d5fe5c9bd5a)
Regression introduced by: https://cgit.freedesktop.org/libreoffice/core/commit/?id=6b4e767cf90e4f384a6b3dd43aa51c09b500b97b author Kohei Yoshida <kohei.yoshida@collabora.com> 2014-11-17 17:37:07 -0500 committer Kohei Yoshida <kohei.yoshida@collabora.com> 2014-11-18 08:31:57 -0500 commit 6b4e767cf90e4f384a6b3dd43aa51c09b500b97b (patch) tree a9a06d522bab185c7df24a6958daf9e59dbefa6a parent 627bfb4e664a3fde23c06aa4dd0809de84ec97c5 (diff) Use group-area listeners during copy & paste if we can. Bisected with: bibisect-44max Adding Cc: to Kohei Yoshida
@Dennis, i thought you might be interested in this issue...
*** Bug 129541 has been marked as a duplicate of this bug. ***
@xisco: i'd suggest to change bug / duplicate between 128975 and 129541, 129541 is the simpler shorter sample which will make it easier for dev's to deal with it ... reg. b.