Bug 76411 - FILEOPEN: Formula Results Incorrect (=0) on File Re-Open
Summary: FILEOPEN: Formula Results Incorrect (=0) on File Re-Open
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
Depends on:
Reported: 2014-03-20 18:39 UTC by C A J
Modified: 2014-03-24 12:45 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:

Example Problem File (6.26 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-03-22 18:16 UTC, C A J
Example Screenshot (64.54 KB, image/png)
2014-03-22 18:18 UTC, C A J

Note You need to log in before you can comment on or make changes to this bug.
Description C A J 2014-03-20 18:39:56 UTC
(Note: the actual LO version I'm using is 4.2.2-2, installed on Arch Linux/xfce with pacman -Syu yesterday, 3/19/2014.)

I opened a Calc file that has long been in use today and a particular set of formulas displayed "zero" as their cell values. The spreadsheet is a collection of my financial records with each line containing a transaction; the main sheet (essentially general journal) has about 11,000 rows and 7 columns of data. The errant formula, running throughout column G, contains:


Column C contains a date; column F contains an account code. The result of the formula should be text of the form "2014ent". While the formulas were all correctly intact, each column G cell displayed a zero.

Further, and I don't know if this is a separate problem, another sheet (general ledger) evaluates the cells from the journal sheet and puts SUMIF results on a layout with accounts in rows and years in columns. Unfortunately, the cells on this sheet have lost their references to the journal sheet. A typical formula here now reads:

    =SUMIF( $G$5:$G$13681,E$4&$B12, $E$5:$E$13681)

Note the presence of a space and the absence of a sheet name at the beginning of the first and third parameters.

I have periodically had a similar problem in another, much simpler, spreadsheet file. This one consists of a single sheet that produces a two-page paper report. I have various text titling information in cells A1, A2, and A3 for the first page and then I have formulas which reproduce that titling text for the second page as follows:

    A42: =A1
    A43: =A2
    A44: =A3

The past behavior, though sporadic, has shown the correct formula in the page-two cells, but displayed zeroes. Just now though, the sheet shows the correct text in A42, but shows blanks in A43 and A44.

In both of these spreadsheets I can display the values for the simple formulas (=YEAR(C11058)&F11058 and =A1) by a hard recalculation with Ctrl-Shift-F9. This does not, however, correct the sheet name deletion in the SUMIF formulas. This hard-recalc solution was suggested on http://ask.libreoffice.org/en/question/31185/after-updating-to-libre-office-4211-calc-stopped-performing-correctly/

I tried reproducing the problem in a new file, but am unable to. I am reluctant to upload either of my two problem files for review since they both contain private and sensitive data. I am happy to provide whatever other information I can and test potential solutions. Please let me know what I can do to help resolve this issue. Many thanks.
Comment 1 Eike Rathke 2014-03-20 19:21:54 UTC
You could send me the problematic file attached to an email. You may OpenPGP encrypt the mail and attachment using my key
0x65632D3A - 2265 D7F3 A7B0 95CC 3918  630B 6A6C D5B7 6563 2D3A
Comment 2 C A J 2014-03-22 18:16:55 UTC
Created attachment 96207 [details]
Example Problem File

Eike, thanks for the offer to privately email you the files, but I was able to remove the sensitive information from the smaller (and less complicated) file, uploaded herewith.

There is a slight difference in the behavior of this file from my original description. Now, cells A42, A43, and A44 show zeroes as their formula results. This is consistent with the (incorrect) behavior in my financial file.

Many thanks for taking a look at this.
Comment 3 C A J 2014-03-22 18:18:33 UTC
Created attachment 96208 [details]
Example Screenshot

Attached is a schreenshot showing the problematic result.

Note also that Help > About identifies Calc as v., although pacman -Q says I have v.4.2.2-2 installed.
Comment 4 m.a.riosv 2014-03-23 03:04:48 UTC
Hi C A J, thanks for reporting.

There is an option for recalculate xlsx files when opening in:
Menu/Tools/Options/LibreOffice calc/Formula/Recalculation on file load - Excel 2007 or newer.
This option works fine with your file.

Also a hard recalc [Ctrl+Shift+F9] solves the issue.

I think not a bug, but I hope Eike can confirm.
Comment 5 C A J 2014-03-23 20:20:24 UTC
Thanks for the suggestion on setting recalc-on-file-load. That solves the problem for the two spreadsheets that were misbehaving.

I'm just curious, though. Is recalc-on-file-load a new option? (Or a config parameter reset by v.4.2.2-2?) The problem with the file I sent has been intermittent and the problem with the 11,000-row file only occurred for the first time last week.
Comment 6 m.a.riosv 2014-03-24 01:13:51 UTC
It was a new option in 4.0.

I think the intention was to allow avoid long recalculations at the opening.
Comment 7 C A J 2014-03-24 12:45:30 UTC
Thanks all 'round. I went ahead and closed out #76411.