Bug 95273 - XLSX import: SUM(cellref) returns 0, while cell contains value != 0.
Summary: XLSX import: SUM(cellref) returns 0, while cell contains value != 0.
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.0.2.2 release
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2015-10-23 09:07 UTC by hugo.jonker
Modified: 2015-10-23 16:27 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:

Attachments
spreadsheet showing the SUM bug on sheet 1, in cell E3. (6.30 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-10-23 09:07 UTC, hugo.jonker 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description hugo.jonker 2015-10-23 09:07:49 UTC 
Created attachment 119901 [details]
spreadsheet showing the SUM bug on sheet 1, in cell E3.

For an imported XLSX spreadsheet (attached), we have the following cell values:

...   |  D          E
2     | 10         =D2                 
3     | 15         =SUM(D3,E2)

Displayed value for E3 is 15.
Expected result: E3 is 25. 

The file was not created by me. As I understand it, it's a Google Docs spreadsheet, which was either exported to XLSX or to LibreOffice and then XLSX.

For added fun: when trying to get a minimal example, I deleted sheet 2. The bug disappeared then. (sheet 2 is a copy of sheet one with extra colors)
Comment 1 m_a_riosv 2015-10-23 09:23:52 UTC 
Hi @hugo, thanks for reporting.

Two ways.

Enable recalculation on load xlsx files:
Menu/Tools/Options/LibreOffice calc/Formula/Recalculation on file load.
Always or Prompt or Never for select.

Doing a hard recalculation, which forces to recalculate all formulas.
[Ctrl+Shift+F9]

On other case only modifications from precedent range(s) are calculated.

But IMO not a bug. Please if you are not agree reopen it.
Comment 2 hugo.jonker 2015-10-23 13:45:48 UTC 
CTRl+SHIFT+F9 corrects the problem indeed.
I am wondering why LO would ever show wrong values though - this happens with that file opened freshly.

Anyway, if you guys don't think displaying the wrong values is a bug, I'm happy to leave this as NOTABUG.
Comment 3 m_a_riosv 2015-10-23 16:27:19 UTC 
(In reply to hugo.jonker from comment #2)
> CTRl+SHIFT+F9 corrects the problem indeed.
> I am wondering why LO would ever show wrong values though - this happens
> with that file opened freshly.
> 
> Anyway, if you guys don't think displaying the wrong values is a bug, I'm
> happy to leave this as NOTABUG.

Please don't misunderstand me, understanding how you like.

The option for ALWAYS update calculations when opening the file is there, and it's very useful to avoid a long time calculations, sometimes several minutes, so disable it for people with large spreadsheets it's fine.

If you have it disable, then no calculations are done, up you enter a value in a cell, but the Autocalculate [F9], only calculate dependencies of the modified cell(s).
For recalculate all is the hard recalc.

Looking inside your file it's has 15, wrong value saved for E3.