Bug 111912 - Spreadsheet is displaying cells with complex formulas and data dependencies with zero or some arbitrary value. These columns are feeds for x-y plots, and they begin to display bizarre results
Summary: Spreadsheet is displaying cells with complex formulas and data dependencies w...
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.1.6.2 release
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2017-08-19 16:10 UTC by Mark Lytle
Modified: 2017-08-23 00:06 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

Attachments
This is one of the worksheets that are acting up. (1.79 MB, application/vnd.oasis.opendocument.spreadsheet)
2017-08-19 17:31 UTC, Mark Lytle 		Details
This is what I get on the second sheet sometimes when a bogus value (in this case '0') appears on one of the columns. (94.23 KB, image/gif)
2017-08-19 17:40 UTC, Mark Lytle 		Details
screenshot of how columns produce bogus data from recent incident. (12.07 KB, image/png)
2017-08-19 17:44 UTC, Mark Lytle 		Details
This is the same file in Apache Openoffice 4.1.3 on Windows 10, no problems (242.30 KB, image/png)
2017-08-21 16:31 UTC, Mark Lytle 		Details
Same problem under LO 5.4 as noted earlier. (262.97 KB, image/png)
2017-08-21 20:57 UTC, Mark Lytle 		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 Mark Lytle 2017-08-19 16:10:34 UTC 
Description:
I have a complex spreadsheet with 4 sheets.  The first panel is for data input and data accumulation and some calculations.  Some of that data is copied to the other sheets and further calculations are done, and those columns are preset up and change from displaying '#value!' to displaying something real and useful when the the cells these are linked to get real data, and that's normal behavior.  Now when entering data and saving the spreadsheet, many of these cells that are in the state of displaying '#value!' suddenly decide their value is '0' or some arbitrary random number.  Those columns are the feed for some x-y plots,and produce bizarre results.

Steps to Reproduce:
1.Add data to spreadsheet
2.Save spreadsheet and close it.
3.Reload spreadsheet, and there they are. You can manually 'clean' these cells by dragging down from the last correctly displayed value to the bottom of the sheet and it will nicely and correctly display '#value!' again.  but after a save and close and reload, you're back in the problem again.

Actual Results:  
Results are bizarre and incorrect plots.  But it's not the plot engine that's at fault.

Expected Results:
Graph output that makes sense.


Reproducible: Always

User Profile Reset: No

Additional Info:
This is happening on two different hardware platforms, on brand new installations and two different releases of Linux Mint.


User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:55.0) Gecko/20100101 Firefox/55.0
Comment 1 m_a_riosv 2017-08-19 17:16:42 UTC 
Is needed attach a sample file to take a look about what is happening.
Please remember thhis is a public site.
Comment 2 Mark Lytle 2017-08-19 17:31:26 UTC 
Created attachment 135666 [details]
This is one of the worksheets that are acting up.

On first sheet, if you look down the columns AF & AG at some point real data 'created' on column AG as the value 414.76, the column to the left is still correct on those rows with '#value1', I keep 'cleaning' it, comes back.  Sometimes it produces a column of zeros below the last real calculation.  This data is then copied to the second, third and fourth sheets for graphs and they become very messed up as they plot the bogus values.
Comment 3 Mark Lytle 2017-08-19 17:40:44 UTC 
Created attachment 135667 [details]
This is what I get on the second sheet sometimes when a bogus value (in this case '0') appears on one of the columns.

The purple line should be one straight line only.  The one coming up from 'zero' from lower left is wrong.
Comment 4 Mark Lytle 2017-08-19 17:44:22 UTC 
Created attachment 135668 [details]
screenshot of how columns produce bogus data from recent incident.

0.000 and 11.170 are bogus should show #value!
Comment 5 Jean-Baptiste Faure 2017-08-20 06:42:01 UTC 
Please, could you try with a recent version? LO 5.1 is not maintained anymore.
You can install 5.3 or 5.4 from the LibreOffice PPA managed by the LO team of Canonical.

In LO 5.4, I can restore a correct state by propagating formulas again. Then adding values in column C of Sheet1 works as expected.

Set status to NEEDINFO, please set it back to UNCONFIRMED once requested
informations are provided.

Best regards. JBF
Comment 6 Mark Lytle 2017-08-21 15:46:04 UTC 
O.K. I now have loaded LO 5.4, and the problem is if anything, worse.  But I understand it better now. The first of the 4 sheets in this somewhat complicated spreadsheet find 'hiden' trendlines in securities, be they cryptos, stocks, currencies, whatever.  Those values are calculated day by day using end of day information.

Columns C, D, & E which are highlighted in light green, are where the current date, the high for the day , and the low for the day, respectively are input.   Colomns AF, AG, AU, AV, AW, AX, BM, AND BN are where the values for the y co-ordinate (price level) of these newly discovered trendlines are calculated for each imput calendar date successly input into column C.  To avoid creating this whole very complex set of graphs everytime you add anothers days data, I've preset about 2000 cells representing about 2000 calendar days in advance to set up the graph, and these rows become active and useful as each new days data is entered.  This used to work flawlessly.

For some reason, the cells lower than the last current row that has it's end of data needs in coulmn C,D, And E met, will sometimes appear to have data in them, and instead of displaying #value!, meaning I guess, an unresolved (as of yet) formula, it sometimes takes on some odd partial value, or zero.  Why that happens I don't know, but it's happening on the second, third, and fourth sheets where the values from the first sheet are copied sheet to sheet to their corresponding places to be plotted on the graphs as a function of time (x-co-ordinate).   The REAL problem seems to be  that only the graphs on sheet 2 are affected, that is these spurious zeros get plotted and ruin the  graphs.  This doesn't happen on sheets 3 & 4 for some reason.  So, two issues, why the spurious numbers, and why only the graphs on sheet 2 fails to ignore them.
Comment 7 Mark Lytle 2017-08-21 16:31:01 UTC 
Created attachment 135706 [details]
This is the same file in Apache Openoffice 4.1.3 on Windows 10, no problems

One of the two machines I'm running these spreadsheets on is a dual boot Windows 10/Linux Mint machine.  On the Windows 10 side have loaded apache openoffice 4.1.3, it is free of the spurious zero's and has not manifested this proble.  Some spurious values here too, but not zero's which  seem to be the main culprit.  All graphs are normal, running 5 of them..
Comment 8 Mark Lytle 2017-08-21 16:32:23 UTC 
Just uploaded picture of Apache OPenoffice on Windows 10. no problems..SLOW though, I'd hate to have to go that route.
Comment 9 Mark Lytle 2017-08-21 20:57:29 UTC 
Created attachment 135712 [details]
Same problem under LO 5.4 as noted earlier.
Comment 10 m_a_riosv 2017-08-21 22:08:17 UTC 
There are spaces instead a blank cell in C71:E1999.

LibreOffice it's more restrictive on how convert text on numbers.

You have options about it on Menu/Tools/Options/LibreOffice Calc/Formula - Detailed calculation settings - Custom - Details, and select the option that fits what you like, I guess the firts 'Generate #VALUE! error'

Resolved as not a bug, please if you are not agree reopen it.
Comment 11 Mark Lytle 2017-08-23 00:06:46 UTC 
You seem to have found the problem.  Everything is now working as it used to. Thank you for your efforts, I never would have found this on my own..