Bug 77469

As an addendum / work around i opened the same spreadsheet file in excel and it recalculated so I saved it and reopened in calc - bingo it now recalculates.

Please Bill, attach the sample file.

Sorry chap, the file is now ok as per my second post. It seems that opening and saving it with Excel 2013 fixed the problem. If it happens again I will take a copy of the file and post it.

Thanks Bill, for now closed as notabug if you see the issue again, please reopen it.

Created attachment 97725 [details]
spreadsheet that doesn't recalc

problem recurred as per attached file - please reopen

Would be nice to have a more specific hint as to which cell on what sheet is not being recalculated and when it is supposed to be recalculated.

Hi Kohei,
opening the sample file, column H is not recalculated,
even activating M/Tools/Cell/Autocalculate or with F9,
only hard recalc works.
(Option in M/Tools/Options/LibreOffice calc/Formula/Recalculate ODF - never)

Reopening the file, activating Autocalculate:

Entering a value e.g. 100 in K1, nothing is calculated.

Entering a value e.g. 100 in F8, only dependencies are calculated H8 and down, but not H3:H7.

Win7x64Ultimate
Version 4.0.6.2 (Build ID: 2e2573268451a50806fcd60ae2d9fe01dd0ce24)
Version: 4.1.6.1 Build ID: a59ce81388f477fc89db57f0c27f222f31884eb
Versión: 4.2.3.3 Id. de compilación: 882f8a0a489bc99a9e60c7905a60226254cb6ff0
Version: 4.3.0.0.alpha0+ Build ID: 0ce6d9bcea7c37a7cb2c42e8393e2e20780bd171
   TinderBox: Win-x86@39, Branch:master, Time: 2014-04-17_00:11:33

With 4.0.6 column 'H' shows blanks after open the file, but has the same behaviour.

Last working:
Version 3.6.7.2 (Build ID: e183d5b)
After open the file an activating Autocalculate do the job.

Ok. Thanks for the specifics.  Setting it to NEW.

So, this may not be a bug.  I'll explain.

(In reply to comment #7)
> Hi Kohei,
> opening the sample file, column H is not recalculated,
> even activating M/Tools/Cell/Autocalculate or with F9,
> only hard recalc works.

The first thing to note is that this file contains incorrect cashed results for Column H, which initially gets displayed upon load.  At this point, pressing F9 won't cause recalculation because the document has not been modified.

Hard recalc forces recalculation because hard recalc ignores the "document not modified" state and go ahead and recalc all formulas unconditionally.

> (Option in M/Tools/Options/LibreOffice calc/Formula/Recalculate ODF - never)

With this option, we assume the stored cached formula results to be correct even if they aren't.

> Reopening the file, activating Autocalculate:
> 
> Entering a value e.g. 100 in K1, nothing is calculated.

This is as expected, since no formula cells reference K1.

> Entering a value e.g. 100 in F8, only dependencies are calculated H8 and
> down, but not H3:H7.

This is also expected.  Autocalculate only calculates affected formula cells when entering a value to a cell.  H3:H7 does not depend on F8 either directly or indirectly, so nothing is wrong here.

So, as far as I'm concerned, I don't see anything broken here.  The only thing is that the cached results are wrong, but that can be fixed by performing hard-recalc and resaving it *once*.  Next time the document is opened, it displays correctly cached formula results upon load.

Also, Auto Recalculate option stays with the document, and this document has it turned off.  If you need auto recalculate enabled, you need to turn it on via menu and resave the document.

I think the main issue is that Calculate [F9] has changed their behaviour from 3.6. When an user press [F9] hopes that a recalculation is done. What I think is the usual way in spreadsheets.

> At this point, pressing F9 won't cause recalculation because the document has not been modified.

And after enter a value e.g. G10, [F9] works the same as autocalculate only dependants are calculated not the all sheet. Perhaps many times there is no visible difference.

And a second one, users usually don't know about hard recalc, and there is no place in the menus or a button or a place in the status bar, to do it.

Even more not too much information about hard recalc, and there are some functions that only are calculated with a hard recalc [Ctrl+Shift+F9]

https://help.libreoffice.org/Calc/Recalculate
A more detailed explanation here, would be very appreciated and helpful to guide the users. 

http://markmail.org/thread/vggjlicfxgpvi2xy

IMO there is a bug with [F9] all must be calculated with the exception of those functions/operations under hard recalc.

Hey Guys you're missing the point. Auto recalc should just turn off on its own - that's a bug ;-)