Description: I have a sheet with a cell which shows an incorrect value. F9 does not cause reevaluation. Copying the exact formula into the cell next to it (not copy and paste cell) gives a different result. The difference is not a number which appears in any cells referenced by the formula. clicking on the cell and pressing return on the formula bar does not update the cell. Copying and pasting the cell onto itself re evaluates the formula to the correct result Steps to Reproduce: I have a copy of the spreadsheet which illustrates the problem. I have no idea how to reproduce the problem. The problem is difficult to spot because the cell contains the result of a sum of many cells. Actual Results: The value in the cell contains the incorrect value and is not recalculated using the normal methods Expected Results: The Cell should never contain the wrong value Reproducible: Didn't try User Profile Reset: No Additional Info: I have a copy of the spreadsheet but do not know how to reproduce the problem
Created attachment 165137 [details] This spreadsheet illustrates the problem Cell AV 284 and cell AW284 contain the same formula. The results differ by the value shown in AX284. The value in cell AV284 is not updated if the sheet is recalculated or if the formula is inspected by clicking on the cell and confirmed by pressing enter where the formula is displayed. Copying and pasting the cell onto itself will cause an update. There does not appear to be any easy means of identifying cells which exhibit this problem. This seems to be a critical bug, in that the cell value is incorrect and very difficult to spot (unless all the calculations are performed by hand). I would expect recalculation of the spreadsheet to refresh all calculated values.
Not reproducible using Version: 7.0.1.2 (x64) Build ID: 7cbcfc562f6eb6708b5ff7d7397325de9e764452 CPU threads: 12; OS: Windows 10.0 Build 19041; UI render: Skia/Raster; VCL: win Locale: ru-RU (ru_RU); UI: en-US Calc: threaded The value in AV284 gets recalculated on F9, and the difference in AX284 gets 0. Please check with a newer version (v.5.1 is EOL since Nov 2016).
Confirm with Version: 7.0.1.2 (x64) Build ID: 7cbcfc562f6eb6708b5ff7d7397325de9e764452 CPU threads: 4; OS: Windows 10.0 Build 19041; UI render: Skia/Raster; VCL: win Locale: ro-RO (ro_RO); UI: en-US Calc: threaded Ctrl+Shift+F9 solves this
Created attachment 165138 [details] before
Created attachment 165139 [details] after
(In reply to BogdanB from comment #3) > Confirm with > Version: 7.0.1.2 (x64) Are you sure? Just checked that the problem was indeed reproducible using v.5.1.0.3, but already not reproducible with version 5.2.1.2 - it recalculated the cell using F9. In 5.1.0.3, it also re-calculated the cell when using Ctrl+Shift+F9, but not F9, as OP describes. Note that showing a wrong value on opening is *not* a bug, since it opens a. ODF (native format) saved by LibreOffice, and uses cached values.
Steps I folowed: Open the file - > 1803,1 Press F9 - > 1803,1 Ctrl+Shift+F9-> 1824,99
Autocalculate it is checked.
(In reply to BogdanB from comment #7) > Steps I folowed: > Open the file - > 1803,1 > Press F9 - > 1803,1 Have you selected AV284 before "Press F9"? F9 only recalculates selection.
Ok. Working in this case Open the file - > 1803,1 Select AV284 Press F9 - > 1824,99 Ctrl+Shift+F9-> 1824,99
(In reply to BogdanB from comment #10) > Ok. Working in this case Not NEW then?
We need from Alan Jones an answer what was expected... And if this info from this bug helped him
Thanks for the rapid response: I can confirm that CTRL shift F9 does recalculate the cell. Where is this in the menus? Selecting the cell and pressing F9 does NOT update the value. I would say that displaying an incorrect value when loading from a file (or at any time that autocalculate is active) is not what the user would expect, and is a bug. This took me quite some time to figure out, I only became aware of it when I was changing the way the values were accumulated an the totals did not match. I will update my OS and install a newer version of Libreoffice, ubuntu 16.04 doesn't have a newer version in the repository. I will leave it to you to decide if the userbase needs to be made aware. I will use CTRL shift F9 often from now on :) Thanks
[Automated Action] NeedInfo-To-Unconfirmed
(In reply to Alan Jones from comment #13) > I can confirm that CTRL shift F9 does recalculate the cell. Thanks for reporting! Good to know; this confirms comment 6. > Where is this in the menus? Indeed it was not in the menus in v.5.1 by default; it's added to the Data menu (Recalculate Hard) in a later version. Note however, that it's impossible to add every of many hundreds of available functions into menus; all of them are manually assignable using Tools->Customize. > Selecting the cell and pressing F9 does NOT update the value. Yes, in v.5.1 that is the problem (that I confirmed). It is resolved in 5.2. > I would say that displaying an incorrect value when loading from a file (or > at any time that autocalculate is active) is not what the user would expect, > and is a bug. This took me quite some time to figure out, I only became > aware of it when I was changing the way the values were accumulated an the > totals did not match. Displaying cached values is *not* a bug - this is the idea behind caching the values; you can make any other software that uses caching to behave similarly, e.g. Excel. But not refreshing on F9 or on changing of referenced data (with auto-calculation enabled) is. The proper fix is to prevent such things from happening, which obviously has already happened. > I will update my OS and install a newer version of Libreoffice, ubuntu 16.04 > doesn't have a newer version in the repository. You may use the related PPA [1]. > I will leave it to you to decide if the userbase needs to be made aware. I > will use CTRL shift F9 often from now on :) So closing WORKSFORME (as it was fixed by an unknown commit). Thank you very much for reporting! [1] https://launchpad.net/~libreoffice/+archive/ubuntu/ppa
Mike, The bug is close, I agree. I want to ask you how could someone make the file do NOT update the formula, like in this file. When I create a new file and I insert formula EVERYTIME I open the file it is updated and ok. How to make this cache happened?...
(In reply to BogdanB from comment #16) I don't know which exact circumstances caused not recalculating of this specific cell in 5.1. Reverse bisecting of the fix could possibly tell. However imagine this scenario: that file was initially set to not recalculate automatically. In that mode, some edits were made, and finally auto-calculation was enabled. That made all values to recalculate at that moment, but the bug prevented refreshing the value in this cell. Which made the save to store this cached value into the file. Note that normal behavior of Calc is to only recalculate "dirty" cells (i.e., those which were made out-of-date by some changes in the referred cells, or having volatile functions like NOW or RANDOM. This allows to skip recalculation of known-correct values; of course, it's assumed that all previous calculations were correct (and when it's not so, that's a bug).
I played with this now. I made this test: - I created a file with 1, 4 and the sum of the 2 numbers. Autocalculate is NOT checked. Change the 4 to 6. The sum is wrong. Save the file and close. - First scenario: Recalculate still NOT checked. Open the file from above. THe sum is wrong. - Second scenario: Recalculate activated. Open the file from above. The sum is wrong. Select the sum. F9 - the sum is correct. Change the number: the sum is wrong. My conclusion: if a formula is created when Autocalculate is NOT checked, this formula will never became autocalculate, even IF autocalculate is activated. It is interesting to know what properties this formula or cell have, because I consider NOT to be ok, to have Autocalculate checked and not to have the real autocalculate for some cells activated. So, if there is a properties, this should change depending of the Autocalculate.
(In reply to BogdanB from comment #18) I'd say it is unexpected, and a bug. Can't test at the moment; please file a separate bug report, and add Eike to it.
But your scenario had saved the file with autocalculation disabled. That is a setting saved per-file; so how did you activated it for the file? The description didn't tell that.
I tried to make a video, but I can't repro anymore. I activated from Data - Calculate - Autocalculate. But in all my test everything is fine wit a new file: when activated it's ok, when desactivated it's ok. This is with a new file. ------------ If this is a per file setting, why in Alan Jones example Autocalcule doesn't autocalculate when activated?... If the file says all formula recalculate now, everything should change, I consider Ctrl+Shift+F9 should be involved when Autocalculate is checked.
Let me try to explain it once more. Any formula cell may be in two states: a. Dirty; b. Up-to-date. A file may be in one of two modes: 1. Auto-calculation off; 2. Auto-calculation on. In either mode, a change in a data marks its dependencies dirty. But in mode #1, this does not trigger following auto-calculation. Auto-calculation (when active) creates a list of all dirty cells in the file, and calculates *those cells*. Which means that *cells that are not dirty are not recalculated*. It is expected that all non-dirty cells already have correct data - that must be guaranteed by previous calculations that made them up-to-date. When you open a file that has auto-calculation enabled, all cached cell values in it are considered correct, and are assigned to cells which are marked up-to-date, which means that they are not recalculated unless something modifies their referenced data. This file has auto-calculated data. It has cached data, which is considered correct. It was generated by a version with a bug, which violated this assumption. That bug needed to be fixed, to stop producing wrong files. It had been done. But old files from that buggy version still contain wrong calculated data, which violates the assumptions..
When you open a file that has auto-calculation enabled, all cached cell values in it are considered correct, and are assigned to cells which are marked up-to-date, which means that they are not recalculated unless something modifies their referenced data. ---> If the file had before closing auto-calculation enabled then this formula should be calculated correct. Because "Auto-calculation (when active) creates a list of all dirty cells in the file, and calculates *those cells*." So, all the dirty cells should be correct before closing the file, and all auto-updated cells should also be correected before closing the file...
(In reply to BogdanB from comment #23) At which point has this cell become dirty to be re-calculated? Its value was read from file. It was up-to-date from the start. At the save time, it was still up-to-date. Its value was stored again.
Ok. I tried now. Disable - create a formula - activate (no change) - close - open again (no change) - because it was created when Autocalculate was disabled. Mixing of Autocalculate and disable this can produce this errors, so this change should be avoided.
(In reply to BogdanB from comment #25) > Disable - create a formula At this step the cell with formula should had became dirty > - activate (no change) At this step (activation of auto-calculate), the dirty cell should had calculated, and have a correct value > - close so that at this step, the correct value should had been written > - open again (no change) - because it was created when Autocalculate was disabled. So if you see it differently (as you describe), please file a bug.
I have just found another aspect to this problem. I opened the file on version 6.3.1.2 on a Mac. pressed F9 on the 1803.10 cell, the value updated to 1824.99 -great Closed the file Reopened the file and the value was back at 1803, WAT!, "cached" value? It would appear that when a value is updated from the cached value it does not mark the file as changed and hence the updated value isn't saved. I have tried with a few different versions (but not the newest) and this behavior is consistent. I can work around this too, but this problem gets more incidious the more we look at it. I humbly suggest that Caching is generally a bad idea, is there an option to disable it altogether? If not then I propose that this option be added so that people who really want the values caching can have that feature. The rest of us that would prefer to have "Reliable" values can turn it off and have the sheet recalculate on load, we have to recalculate anyway based on these findings.
Created attachment 165198 [details] video In my new video when I activated Autocalculate after opening again the file (the result was wrong at opening, like in Alan case) everything worked ok. Why in Alan case Autocalculate doesn't worked this way?...
(In reply to Alan Jones from comment #27) > I opened the file on version 6.3.1.2 on a Mac. > pressed F9 on the 1803.10 cell, the value updated to 1824.99 -great > > Closed the file > Reopened the file and the value was back at 1803, WAT!, "cached" value? F9 does not change your file. You did not type anything; you did not delete anything. Re-calculation itself is not a modification.
(In reply to BogdanB from comment #28) > Why in Alan case Autocalculate doesn't worked this way?... In Alan's case, it also worked exactly that way. Except for the bug, that resulted in a wrong result in the problematic cell, so that a wrong number was considered correct, and written into the file. The bug was fixed, but the file from the bad version still contains wrong number.
Hi, I disagree that pressing F9 or Control Shift F9 do not result in modification. These recalculations cause all of the dependencies to update. How can a user have any faith in a product if what is on the display when they close the file is not saved. As for the talk of autocalculate on/off. It is and always has been on. So lets recap, in order to see the correct values in a spreadsheet and save the values that I see. I have to load the file, press F9 on cells which I think might be displaying the wrong values (or press control shift F9), add something to a random cell (which has nothing to do with the main calculations in the spreadsheet) remove whatever I added and then save the file. Alternatively I can turn autocalculate off press control shift F9 turn autocalculate back on and close the file. This defense of the undesirable/unexpected/misleading and potentially very costly behaviour is not conducive to a good product. I don't believe that we have even figured out how the inconsistency was introduced in the first place, which makes this even more significant a finding. I have just opened this file in StarOffice 8 (as a really old benchmark), the correct values are always displayed. Somewhere along the road, LibreOffice has introduced this undesirable effect.
Reopened is wrong status here. See linked bug and is links.
There was a bug in an old version, that created *invalid* files. You have such files. Since then, the bug that created such files has been fixed (already in 5.2). Since then, doing the same actions that you did initially would produce correct results. This is WORKSFORME. The behaviour that information read from file is trusted is correct. This is the invariant that must not be violated. That it was violated by 5.1 was exactly the bug that was fixed. The behaviour that recalculation is not considered a file modification is correct. Otherwise, e.g. opening a file containing RAND() would be already modified on opening, because RAND is volatile, and its recalculation happens on load. This is resolved WORKSFORME. Any follow-up suggestions should be filed separately.