Bug 101694 - Formula displays wrong number
Summary: Formula displays wrong number
Status: RESOLVED INSUFFICIENTDATA
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.0.4.2 release
Hardware: All Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-08-24 08:10 UTC by bordmail4
Modified: 2017-07-27 12:02 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
calc file exemplyfying the error (28.34 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-08-24 08:11 UTC, bordmail4
Details

Note You need to log in before you can comment on or make changes to this bug.
Description bordmail4 2016-08-24 08:10:08 UTC
User-Agent:       Mozilla/5.0 (Windows NT 10.0; rv:47.0) Gecko/20100101 Firefox/47.0
Build Identifier: LibreOffice 5.0.4.2

Several times (rarely) I cought LibreOffice not updating formulas in calc but now I have proof a file I'm working: R5 cell is a formula referring to other cell (=L5*N5*(1+O5+P5)) and dsplays the corect 17.41 result. U5 copies the R5 value (=R5) but dysplays 10.24 instead of 17.41?
Same for the other U cells.
The list is filtered, perhaps that's why?

Reproducible: Sometimes

Steps to Reproduce:
1. no ideea, just happens. I worked on that file on the 11 of august than copied it on 22aug unchanged to add some details. When I copy that row on a fresh page, the values are correct.
2. did not change those cells since 11 august
3. at any rate, shouldn't they display correctly ?



[Information automatically included from LibreOffice]
Locale: en-US
Module: SpreadsheetDocument
[Information guessed from browser]
OS: Windows (All)
OS is 64bit: no


Reset User Profile?No
Comment 1 bordmail4 2016-08-24 08:11:56 UTC
Created attachment 126999 [details]
calc file exemplyfying the error
Comment 2 Aron Budea 2016-08-24 19:07:20 UTC
Displaying an outdated value upon opening is not incorrect by itself, as it displays a stored result (there's a setting to always recalculate formulas upon opening, plus recalculation can be forced with Ctrl-Shift-F9 key combination), if there's an issue, it happened when the value was stored with the spreadsheet.
That might be difficult to find.
Comment 3 Jean-Baptiste Faure 2016-08-26 04:57:42 UTC
Did you use another spreadsheet software to edit this file?

Does F9 update the U5 cell for you?

Best regards. JBF
Comment 4 Eike Rathke 2016-09-03 09:54:46 UTC
The cell value of U5 is stored in the file as 10.2426643, opening the file does not recalculate unless activated under Options or manually. F9 on cell U5 works, so does Shift+Ctrl+F9 to recalculate all.

Question remains, how was the not recalculated value saved to the document? Was there once AutoCalculate disabled when pasting the formula? Any details on how you copied data?
Comment 5 bordmail4 2016-09-04 04:10:52 UTC
Thanks for the interest.

No, I never knew it existed or turned off recalculate. Recalculation on file load, to my surprise, is set to never recalculate for both Excel 97 and ODF files (this is my home PC, I think I may have worked at home on that project). 
In my line of work, I do large spreadsheets with all sorts of formulae from acquisition costs to discount levels, so turning off recalculate would be suicidal !
2 causes jump to mind:
- the recovery process saved the file with some default settings (sometimes it happens, I also work with 10 calc files open, each several sheets deep).
- from an update

I'll check Monday on the PC at work.

As for how I copied the data, I used a file from a previous project, deleted and wrote the new data. Not sure I dragged top down in the formulae cell ranges or used them as they were..
Comment 6 Eike Rathke 2016-09-04 12:36:54 UTC
Recovery is a good hint. It might be that the recovery state was after pasting but before recalculation of the pasted formulas.
Comment 7 Buovjaga 2016-09-24 18:27:45 UTC
(In reply to bordmail4 from comment #5)
> I'll check Monday on the PC at work.

Did you check it yet?
Comment 8 bordmail4 2016-09-30 03:01:07 UTC
For me F9 and ctrl shift F9 did not work.

But I got another hint: it seems to be Sum related.
I have a list of products with their prices and the total sum of the order, from which sum are derived some percentages (freight, bank, import expenses) that are applied back in formulas on each product row, resulting the minimal selling price. I changed some quantities in that sheet and wondered at the weird results: the sum did not actuate from the old value, throwing off the calculations, but the formulas did change on the fly.F9 did not work, CTRL Shift F9 did not work. Delete the sum cell and write afain that formula and it displays correct and solves the problem but not the issue....
Comment 9 Aron Budea 2016-09-30 03:08:14 UTC
Before looking into this further, could you check with a current LibreOffice version (5.1.5 or 5.2.2)?
Comment 10 QA Administrators 2017-06-28 12:36:50 UTC Comment hidden (obsolete)
Comment 11 QA Administrators 2017-07-27 12:02:20 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

MassPing-NeedInfo-20170727