Bug 91947 - Calc does not compute a formula in particular Calc file (OSX)
Summary: Calc does not compute a formula in particular Calc file (OSX)
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All macOS (All)
: medium normal
Assignee: Not Assigned
Depends on:
Reported: 2015-06-08 20:07 UTC by maurice
Modified: 2016-10-10 11:13 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

File that produce the bug (11.16 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-06-08 20:07 UTC, maurice
New bug file with AutoCalculate turned on (11.16 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-07-12 19:14 UTC, maurice

Note You need to log in before you can comment on or make changes to this bug.
Description maurice 2015-06-08 20:07:13 UTC
Created attachment 116385 [details]
File that produce the bug

I am working with Mac OS X Yosemite Version 10.10.3 and LibreOffice Version

In cell "test1.A3" of the attached file, I have a simple formula which just sums up the content of cell "test1.A2" plus 1, but it get stuck to an arbitrary number instead.

I removed everything from the file that did not trigger a new computation and I end up with a very simple file which (for a reason that I would like to understand) still does not compute correctly.

The content of test1.A2 is 1985 so logically I would expect 1986 (1985+1) to appear in test1.A3 but instead I have 1866, weird!

I have seen several action that triggers the correct computation:
- deleting, adding or moving sheets
- Perform a "Clear Direct Formatting" on test1.A3, then save and reopen the file

It seems that the problem only occurs on OS X if I refer to other people testing the file (http://ask.libreoffice.org/en/question/51396/calc-does-not-compute-a-formula-why/).
Comment 1 Alex Thurgood 2015-06-09 07:40:52 UTC
I see the same incorrect result as you with the file you posted.

If I type in =A2+1 into cell B3, I get 1986, so the question is, how did you create the formulate in A3 ?

Did you type it in, or did you drag down from cell A2 ?
Did you apply any particular formatting to those cells beforehand ?

Setting NEEDINFO pending receipt of requested information. Please set back to UNCONFIRMED once you have provided detailed steps of how you created this file, or how to reproduce the problem systematically.

Tested on

Build ID: d67f4ed258c2103e07c7a2bd94d16b604a20a8aa
Locale: fr-FR (fr.UTF-8)

OSX 10.10.3
Comment 2 Alex Thurgood 2015-06-09 07:46:03 UTC
If I force the recalculation of the cell contents, the correct value is displayed.
Comment 3 Alex Thurgood 2015-06-09 07:47:37 UTC
I notice that "change tracking" is on for this file. Does it make a difference if you turn that function off ?
Comment 4 Alex Thurgood 2015-06-09 07:54:33 UTC
In bug 39828, something similar appears to happen with calculations in Writer when change tracking is activated. Could be that the same root cause is the problem.
Comment 5 maurice 2015-06-09 09:09:46 UTC
In regard to how this file was created: unfortunately, I don't know because I am not the original author of this file. The author asked me to have a look at the file because it was not correct. I analyzed the file and striped down everything from the file that was not causing the bug in order to keep the smallest bugged file as possible.

On my side, when I perform a recalculation (Tools > Cell Contents > Recalculate), it does not recompute correctly but it keeps the "1866" value.

On my side, when I disable the "change tracking" as you mentioned (File > Properties... > Security > Record changes), it does not help, even if I save and reopen the file.
Comment 6 Alex Thurgood 2015-06-09 15:19:09 UTC
Without detailed steps on how this occured or how to reproduce it, the bug report will never be confirmed. Yes, we have an anomaly in that file, but without knowing where it came from or how it was reproduced, it will simply be ignored.
Comment 7 maurice 2015-06-12 07:54:27 UTC
It seems to me that the handling a this bug report fits perfectly the ostrich-like approach!
Comment 8 Alex Thurgood 2015-06-14 17:27:12 UTC
In what way is it ostrich-like ? If no one else on OSX experiences the problem in a reproducible manner so that we can understand how and why it exists (and hence possibly see a way to fix it), and if the problem goes away on master when one forces a recalculation of the sheet, then the problem is a minor annoyance at best, but still doesn't bring us any further in understanding what caused it. Unless you happen to know lots of Mac users and developers that can explain how to reproduce the problem and suggest a fix, the project itself doesn't have those resources, and hence this bug is unlikely to be either confirmed or fix. Nothin ostrich-like about it, just a fact of life for a mainly volunteer driven project like this one.
Comment 9 maurice 2015-06-14 17:56:54 UTC
First of all, I am very grateful for this very nice piece of software. I thanks everyone that invests time or any resources in this project and, more generally, in any FOSS-related project.

We have a file that shows the problem here. You mentioned earlier that you were able to see the incorrect calculation. To me, the problem is not that no one is able to rebuild this file yet, but the fact that we have a file, which seems to be valid considering the open document standards, that LibreOffice is not computing it correctly.

So, we have the data that reproduce the problem but we just don't know how we managed to build these data. If that is sufficient to discard the whole problem, then this what I call the ostrich-like approach.

Why is LibreOffice not computing correctly? Is the file valid? If yes, then why should we bother about how this file was build. If no, then the problem is somewhere else, it is not that LibreOffice is not computing correctly but that LibreOffice allowed someone to build an invalid file, and then it might useful/vital to know how we build this file. Let's not forget think this OO principle: abstraction!
Comment 10 Alex Thurgood 2015-06-14 18:24:41 UTC
XML in content.xml :

table:style-name="ro3"><table:table-cell office:value-type="float" office:value="35528" calcext:value-type="float"><text:p>35528</text:p></table:table-cell><table:table-cell/></table:table-row></table:table><table:table table:name="test1" table:style-name="ta1"><table:table-column table:style-name="co7" table:default-cell-style-name="Default"/><table:table-row table:style-name="ro5"><table:table-cell table:formula="of:=4+60*QUOTIENT(YEAR([test0.$A$1])-4;60)" office:value-type="float" office:value="1984" calcext:value-type="float"><text:p>1984</text:p></table:table-cell></table:table-row><table:table-row table:style-name="ro5"><table:table-cell table:formula="of:=[.A1]+1" office:value-type="float" office:value="1985" calcext:value-type="float"><text:p>1985</text:p></table:table-cell></table:table-row><table:table-row table:style-name="ro5"><table:table-cell table:style-name="ce2" table:formula="of:=[.A2]+1" office:value-type="float" office:value="1866" calcext:value-type="float"><text:p>1866</text:p></table:table-cell></table:table-row></table:table><table:table table:name="s9" table:style-name="ta1"><table:table-column
Comment 11 Alex Thurgood 2015-07-12 13:36:27 UTC
Adding Regina to CC : Regina does anything by chance spring to mind to you here ? 

I am not au fat with the complexities of ODF XML structure in Calc, so no idea why we have this spurious 1866 inserted that is not a result of the preceding calculation sequence.
Comment 12 Regina Henschel 2015-07-12 14:29:57 UTC
A "recalculate" is missing. Press F9 to recalculate.

There exists a setting "AutoCalculate" which is stored within the file. This is off in the example document. The result it, that all changes are not recalculate immediately, but you have to force it with F9. In file source the setting is in the part settings.xml. 

The setting "AutoCalculate" has been in Tools > Cell Contents in UI. It has been moved to menu Data > Calculate for LibreOffice 5.

A user side setting was introduced in Tools > Options > Calc > Formula about recalculating on opening. When you set it to "user prompt" you can track, whether this has an effect in this case in addition.
Comment 13 Alex Thurgood 2015-07-12 18:44:35 UTC
Thank you Regina. I had already mentioned in my comment 2 to the OP that a forced recalculate gave me the correct answer, so it seems that this is indeed down to the missing "recalculate" setting.
Comment 14 maurice 2015-07-12 19:14:37 UTC
Created attachment 117193 [details]
New bug file with AutoCalculate turned on
Comment 15 maurice 2015-07-12 19:15:31 UTC
Hi there,
I turned on the AutoCalculate in Tools > Cell Contents and closed the file, and the re-open it again. The problem is still there.
I checked the settings.xml file and the value has been correctly set from false to true.
The new file is attached in my previous comment.
Comment 16 Joel Madero 2015-10-18 01:19:49 UTC
As has been said, we have one test document (the second one is based on the first one it appears so...it's the same document) that we have no origin for, no idea what software created it, etc...

Without reproducible steps that can be done with a fresh document or a description of how this file was made (for instance, was it even made with LibreOffice?) there isn't anything we can do and the bug will never be confirmed.

For now I am throwing this back to NEEDINFO - please do not set it back to UNCONFIRMED unless you can answer either: (1) how was the file made or (2) how can we create a file within LibreOffice to reproduce this issue from scratch.

The bug will be closed as INVALID in 7 months according to our bug cleanup policies. Thanks
Comment 17 maurice 2015-10-20 17:25:44 UTC
Is the file valid?
Comment 18 Xisco Faulí 2016-09-11 20:08:13 UTC Comment hidden (obsolete)
Comment 19 Xisco Faulí 2016-10-10 11:13:40 UTC
Dear Bug Submitter,

Please read this message in its entirety before proceeding.

Your bug report is being closed as INSUFFICIENTDATA due to inactivity and
a lack of information which is needed in order to accurately
reproduce and confirm the problem. We encourage you to retest
your bug against the latest release. If the issue is still
present in the latest stable release, we need the following
information (please ignore any that you've already provided):

a) Provide details of your system including your operating
   system and the latest version of LibreOffice that you have
   confirmed the bug to be present

b) Provide easy to reproduce steps – the simpler the better

c) Provide any test case(s) which will help us confirm the problem

d) Provide screenshots of the problem if you think it might help

e) Read all comments and provide any requested information

Once all of this is done, please set the bug back to UNCONFIRMED
and we will attempt to reproduce the issue. Please do not:

a) respond via email 

b) update the version field in the bug or any of the other details
   on the top section of our bug tracker

Warm Regards,
QA Team