Bug 95156 - Incorrect calculations on large spreadsheet
Summary: Incorrect calculations on large spreadsheet
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All Linux (All)
: low major
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Cell-Formula
  Show dependency treegraph
 
Reported: 2015-10-18 13:13 UTC by David
Modified: 2019-10-08 17:10 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Financial Analyses (90.69 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-10-18 13:13 UTC, David
Details

Note You need to log in before you can comment on or make changes to this bug.
Description David 2015-10-18 13:13:14 UTC
Created attachment 119714 [details]
Financial Analyses

Simple calculations on a large spreadsheet quit calculating correctly. Spreadsheet is complex, but simple calculations begin failing. This problem existed about 8 years ago with Open Office as well. I can forward the spreadsheet and include a graphic of the issue. If I copy the formula from one cell and paste to another cell - the new one works - for a limited amount of time.

Program works fine in Gnumeric - but their graphics are ridiculous!
Comment 1 Oliver Brinzing 2015-10-18 14:48:30 UTC
i think i can confirm with a lo  5.1.0.0.alpha1+  e2a7cb31ed5b413ea2f846d0a5c562d1ad97195a
debug build on win 7 64bit

example:

- changing value in cell WF-P&L.S2 from 42.77 to 50 
  will set WF-P&L.O31 from $38,08 to $11.795,30.

- changing value WF-P&L.S2 back to 42.77 results in $3.911,07.

- pressing shift+ctrl+f9 to do a full recalc changes the value back to $38,08

i seems, the spreadsheet is not completely recalculated after a value changes
Comment 2 Joel Madero 2015-10-18 17:55:05 UTC
Per the description I am updating the version (version is oldest version that shows the issue, not latest tested on)

That being said - I can confirm the behavior but what I can say is that it's very very unlikely the problem can be diagnosed with a sample like this. Developers looking at the xml of the spreadsheet are going to just be irritated at how complex it is and 99.999% of the data will be irrelevant for digging through and finding the problem.

I'll mark it as:
New - confirmed
Major - apparent loss of data;
Low - because we have a single test case that is overly complex and very hard to actually determine what is going on. If you can cut it down as much as possible and demonstrate the same issue, we can reconsider the importance.
Comment 3 Oliver Brinzing 2015-10-27 18:45:15 UTC
some remarks:

- the spreadsheet uses special iteration settings for calulation:

  <table:calculation-settings>
     <table:iteration table:status="enable"
       table:steps="1000" 
       table:maximum-difference="0.0001"/>
   </table:calculation-settings>

  if have another (confidential) spreadsheet with same issue. it also uses iterations.
  so maybe the root cause is the use of iterations?

- aoo 4.1.1 has same problem

- saving as *.xls and calculate with excel 2010 works for me.
Comment 4 David 2015-10-27 21:23:56 UTC
(In reply to Oliver Brinzing from comment #3)
> some remarks:
> 
> - the spreadsheet uses special iteration settings for calulation:
> 
>   <table:calculation-settings>
>      <table:iteration table:status="enable"
>        table:steps="1000" 
>        table:maximum-difference="0.0001"/>
>    </table:calculation-settings>
> 
>   if have another (confidential) spreadsheet with same issue. it also uses
> iterations.
>   so maybe the root cause is the use of iterations?
> 
> - aoo 4.1.1 has same problem
> 
> - saving as *.xls and calculate with excel 2010 works for me.

Yes, I tried both automatic and setting iterations. However, it didn't make any difference. Please set back to automatic and try it and you should still be able to verify the problem persists. The reason I tried that option was because I thought setting the options manually may improve iterative cycles to finish the complex calculations dependant on each other.

Yes, I never have this issue in Excel. However, I believe in OS and hopefully this product can be made to compete. Some day I hope to have enough money to help support this effort. I am not alone in working with data in complex ways. That tool is unique and informative for predictions. I am not a programmer, but I do SAS analytics as well as SQL.

Thank you!
Comment 5 QA Administrators 2016-11-08 12:06:03 UTC Comment hidden (obsolete)
Comment 6 Oliver Brinzing 2018-10-07 08:59:16 UTC
reproducible with

Version: 6.1.2.1 (x64)
Build-ID: 65905a128db06ba48db947242809d14d3f9a93fe
CPU-Threads: 4; BS: Windows 10.0; UI-Render: Standard; 
Gebietsschema: de-DE (de_DE); Calc: 

but *not* with:

Version: 6.2.0.0.alpha0+ (x64)
Build ID: 315e585f283e6e4a5f1ac11e7d17a638d04e82b0
CPU threads: 4; OS: Windows 10.0; UI render: default; VCL: win; 
Locale: de-DE (de_DE); Calc:
Comment 7 QA Administrators 2019-10-08 02:28:57 UTC Comment hidden (obsolete)
Comment 8 Oliver Brinzing 2019-10-08 17:10:05 UTC
(In reply to Oliver Brinzing from comment #1)
> example:
> 
> - changing value in cell WF-P&L.S2 from 42.77 to 50 
>   will set WF-P&L.O31 from $38,08 to $11.795,30.
> 
> - changing value WF-P&L.S2 back to 42.77 results in $3.911,07.
> 
> - pressing shift+ctrl+f9 to do a full recalc changes the value back to $38,08
> 
> i seems, the spreadsheet is not completely recalculated after a value changes

reproducible with:

Version: 6.1.6.3 (x64)
Build-ID: 5896ab1714085361c45cf540f76f60673dd96a72
CPU-Threads: 4; BS: Windows 10.0; UI-Render: Standard; 
Gebietsschema: de-DE (de_DE); Calc: 

but no longer reproducible with:

Version: 6.2.7.1 (x64)
Build-ID: 23edc44b61b830b7d749943e020e96f5a7df63bf
CPU-Threads: 4; BS: Windows 10.0; UI-Render: Standard; VCL: win; 
Gebietsschema: de-DE (de_DE); UI-Sprache: de-DE
Calc: 

Version: 6.3.2.2 (x64)
Build-ID: 98b30e735bda24bc04ab42594c85f7fd8be07b9c
CPU-Threads: 4; BS: Windows 10.0; UI-Render: Standard; VCL: win; 
Gebietsschema: de-DE (de_DE); UI-Sprache: de-DE
Calc: 

Version: 6.4.0.0.alpha0+ (x64)
Build ID: b0b8851021cee26412182b51ed28fe574632a4d6
CPU threads: 4; OS: Windows 10.0; UI render: default; VCL: win; 
Locale: de-DE (de_DE); UI-Language: en-US
Calc: threaded