When there is a large amount of data (hence a lot of recalculation is needed), Calc is sometimes "lazy" to recalculate all the formulas. Instead of attaching a large spreadsheet, here is a description to make one quick: From A1 to AD500 (1500) cells) put =RAND(). From A502 to AD502 put =AVERAGE(A..AD1:A..AD500) (i.e. the average of the cells above it) From AJ1 to AJ500 put =AVERAGE(A1..500,AD1..500) (i.e. the average of the cells on its left) AF502 should be =AVERAGE(A502:AD502) (i.e. the average of the vertical averages) AJ502 should be =AVERAGE(AJ1:AJ500) (i.e. the average of the horizontal averages) AH502 should be =+AF502-AJ502 (i.e. always ZERO) Now pressing F9 repeated times, depending which cell is active, only some of the averages are recalculated and AH502 gets non-zero values. If I click into the big part of the spreadsheet then the A502..AD502 cells are recalculated and AF502 and AH502, but not AJ1..AJ500 and AJ502. If I click into the A502..AD502 range then it is the same as above. If I click into an empty cell it is again the same as above. BUT If I click into AJ1..AJ500 then everything is recalculated as it should be and AH502 is always zero. Thanks for looking into it. P.S: I know it is not the latest Calc, but Debian 10 has it as the latest.
It's very old, released in 2019, this days it's coming 7.5 version. But please attach a sample file, so it can be tested with releases in production.
First, FWIW, I followed your instructions - I hope I got them correctly - but on Windows, not Debian. Version: 7.4.5.1 (x64) / LibreOffice Community Build ID: 9c0871452b3918c1019dde9bfac75448afc4b57f CPU threads: 4; OS: Windows 10.0 Build 19044; UI render: default; VCL: win Locale: en-US (es_AR); UI: en-US Calc: CL The resulting ods file weights about 288KB (294 644 bytes). Only _part_ of the results were replicated. I'm not sure I understand which behavior you consider incorrect, or at least unexpected. What part of the resulting behavior you consider to be unexpected? What should be the expected behavior instead? FWIW, every time I attempted "Recalculate Hard" (CTRL+SHIFT+F9), I always got cell AH502 to result in zero, in whichever cell address I was focused on. In Debian, you have backports available, if you can use them. There are also newer packages in sid, of course. In any case, please reply with the answer to what you consider unexpected and what should had been the normal/expected behavior instead. https://wiki.debian.org/LibreOffice https://packages.debian.org/search?keywords=libreoffice
Without taking a look at a problematic file, and reproduce the issue, it's not possible to verify the problem. Try to attach the file after eliminate on it what private data it has.
Thanks for looking into it. Actually I had this problem many times in the past with large tables, linked (or chained) functions and high number of rand() fields. This is a particularly cheeky problem, because one cannot feel certain that the table is up-to-date even after an F9. Yesterday, I could generate this problem with the a.m. very simple spreadsheet as well (what I see, you reproduced). Today, after reboot, reload, the problem disappeared. So, surely it is not with a particular spreadsheet/file, but something probably with memory allocation, or alike. I don't know. When I had this problem I always had high memory usage in the system. Since it is not file dependent, but environment, the best I can do, next time when I see this problem, I make some screenshots and send those.
Oh, sorry: So the expected behavior is that AH502 is always zero. The unexpected behavior was that only part of the table is updated and so, some formulas had wrong result, finally the AH502 was non-zero. But again, it is not always the case (today I could not reproduce it yet).
Well, there is F9 https://help.libreoffice.org/latest/en-US/text/scalc/01/06080000.html and there is CTRL+SHIFT+F9 https://help.libreoffice.org/latest/en-US/text/scalc/01/recalculate_hard.html
Thanks. In version 6 hard recalculate and RAND.NV were/are not there yet. So, this might be the reason. Still it is strange that mostly it works, sometimes not. (Maybe this is why it was introduced...?) But I will upgrade to the most recent version and check again. Thanks again, I will set it to resolved/fixed assuming that it will not occur in newer releases.
I comment to update because along the time, some bugs were corrected, about calculation. But if hard recalculate doesn't work, maybe is needed to verify that the formulas are done in the right way.
Is this supposed to be fixed with LibreOffice 7.5.1.2 ? Because that was the update I installed after this recalcuate bug made Calc impossible to work with. The only way I can get a recalculate done is to close, then reopen the spreadsheet! My setup: Lenovo ThinkCentre i5-6400, 16gb RAM, BIOS: (22/12/2021), UEFI. O/S: Windows 10 pro 10.0.19044, with latest LibreOffice 7.5.1.2 x86_64 (Installation: 'repaired' using LibreOffice MSI today).
The bug is still there in LibreOffice 7.5.1.2 x86_64 running under Windows 10 pro. BTW: LibreOffice 7.5.1 release notes don't mention a recalculate bug. So how can you fix an issue without releasing a build with the fix in it? This isn't a minor fault. It is a massive one. That's why I reopened the issue.
Previously I said that closing the sheet and reopening fixed the issue. I was wrong. Closing and reopening fixes the issue for some formulas but not for all. Nothing fixes it. Even closing the application and rerunning it does not fix the issue! This issue is seen with a big spreadsheet: thousands of rows of data, scores of columns and at least 5 or 6 sheets. With some formulas dependent on other forumula results. There are no macros nor used-defined functions in this worksheet, but is has lots of named ranges used in forumlae. I don't want to insult anyone but it seems to me, your code and UI tests are way too limited. How are you doing you UI tests? I don't expect devs to do manual tests, but I expect sophisticated tests to test the code & formulas. Re: Jollytall's previous test using RAND(). That is not a sensible test. One is supposed to know the expected result of a formula. So any data created by RAND() will be pointless.
Stil no feedback. Is everyone asleep? According to m.a.riosv (2023-02-14 13:12:15 UTC), above: "But if hard recalculate doesn't work, maybe is needed to verify that the formulas are done in the right way." <- As I see it - if the "formulas are wrong", I expect to see an error, failure to compute. But Calc is brokw and whatever error is present is NOT being reported to the system. BTW: Windowns has a fault tracking system: It seems you're ingnoring the Windows fault tracking system. In favour of what? Does LibreOffice have its own fault logging? If so where did you hide it? This software is not behaving according to any normal rules I've seen. Where can I read the bug and exception logs which are breaking this Recalculation? (for example).
I'm wrong or there is not an attached file for text. So still no feedback from your side.
Mark, please open a separate bug report with your issue with detailed reproduction steps, and a sample spreadsheet (if needed).
(In reply to Mark Pawelek from comment #11) > Re: Jollytall's previous test using RAND(). That is not a sensible test. One > is supposed to know the expected result of a formula. So any data created by > RAND() will be pointless. Sorry, I just now found this earlier comment. I do not see why it was not sensible. Whatever rand numbers we add, it is expected that the total we get is the same regardless if we add the numbers of a table first in rows and then in columns or vice-versa. So the difference of the two totals must be zero. That is a clear expected result. If all cells have fixed numbers then doing recalculate many times should not change the results, so to see the bug we need some changing cells, e.g. time() or need manual update of cells to new values. Rand() seems the easiest way to change many cells in one Recalculate cycle. To the main issue: Clearly some part of the recalculation was not done when the system had too much to do. Unlike in Mark's case, it was not needed to have multiple sheets or data ranges. It was enough to have too many cells to recalculate in one go. Still, I have not seen this bug again since I upgraded to 7.5.4.2., so for me the issue is solved.