Bug 112434 - incorrect formula calculation after pasting when some of pasted cells would result in a REF result
Summary: incorrect formula calculation after pasting when some of pasted cells would r...
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:
Blocks: Paste Cell-Formula
  Show dependency treegraph
Reported: 2017-09-16 20:14 UTC by MD
Modified: 2018-05-30 16:47 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

srceenshot (493 bytes, text/plain)
2017-09-17 09:06 UTC, Xavier Van Wijmeersch
srceenshot (60.94 KB, image/png)
2017-09-17 09:40 UTC, Xavier Van Wijmeersch

Note You need to log in before you can comment on or make changes to this bug.
Description MD 2017-09-16 20:14:22 UTC
On opensuse leap 42.2
repeated on different PC but still running leap 42

Steps to Reproduce:
1 Create a new spreadsheet
2 goto cell A150 and enter a 1
3 goto cell B150 and enter formula =sum(a147:a153) : cell b150 displays 1
4 copy cell b150 to clipboard ctrl C
5 goto cell b300
6 select cells from b300 to b1
7 do paste ctrl V 
variant further steps
goto cell b150
copy with ctrl c
select cells B150 to B3
paste with ctrl V
   still showing zeros every where
delete cells B1 to B3
repeat past into cells b150 to B3
   correct result shown

Actual Results:  
all cells from b1 to b150 display 0
formulae shown in cells are correct

Expected Results:
cells B1 to B3 should display REF as indicated by their formulae
cells B147 to B153 should display 1

Reproducible: Always

User Profile Reset: No

Additional Info:
[Information automatically included from LibreOffice]
Locale: en-US
Module: SpreadsheetDocument
[Information guessed from browser]
OS: Linux (All)
OS is 64bit: yes
Builds ID: LibreOffice

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.113 Safari/537.36
Comment 1 Xavier Van Wijmeersch 2017-09-17 09:06:38 UTC
Created attachment 136294 [details]
Comment 2 Xavier Van Wijmeersch 2017-09-17 09:07:25 UTC
Its normal that you have a ref error because B1 is the first cell off column B, and the formula is looking to B1 minus wish not exist. Have a look at the screenshot you will see B4 reference to A1(:A7) wish is the first cell off column A. So B1 to B3 will have a ref error. I think its not a bug.

Best regards

Build ID: 61d85c4e7c30ea0f5242d927b7456190020b4fbe
CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); Calc: group
Comment 3 Xisco Faulí 2017-09-17 09:39:43 UTC
Closing as RESOLVED NOTABUG based on comment 2
Comment 4 Xavier Van Wijmeersch 2017-09-17 09:40:38 UTC
Created attachment 136295 [details]
Comment 5 MD 2017-09-20 16:49:46 UTC
comment #2. You have misunderstood this bug report

The bug is that cells in column B are incorrect after the paste operation.
Cells with a REF shown in the formula should display REF not 0.
Cells B147 to B153 should display a 1 not 0.

Any formula that then references cells B147 to B153 uses the value of zero and gets incorrect results.

This results in a spreadsheet quietly miscalculating. 

Clearly I have a simple case that makes it obvious a bug is present.
Others may have more complex sheets and be unaware their calculations are wrong.
This is why I marked it critical. The spreadsheet is not fit for purpose. 

I tried to reproduce this before making this comment and instead of cell A150 as my starting point I used cell A7. The sheet worked correctly. 
However when I used cell A150 again I got the problem.
Comment 6 MD 2017-09-20 17:24:54 UTC
repeated using version LibreOffice_5.4.1_Linux_x86-64_rpm.tar.gz

I installed this version locally in my home folder while still having the 
latest OpenSUSE-Leap42 version installed.

I assume they should not interfere.
Comment 7 MD 2017-09-20 17:26:09 UTC
Further investigation indicates the paste operation behave correctly
if the paste is from cell B1 to B99
but fails if the paste is made after cell B100
Comment 8 MD 2017-09-20 17:32:20 UTC
Also: If the sheet is saved then re-opened the cells are calculated correctly
Comment 9 Xavier Van Wijmeersch 2017-09-20 20:37:46 UTC
Your wright I misunderstood the description.
I have made a new test, the only problem I have is the pasting bit.
This result I have when saved it and re-opened it is

"cells B1 to B3 should display REF as indicated by their formulae
cells B147 to B153 should display 1"

Build ID: 61d85c4e7c30ea0f5242d927b7456190020b4fbe
CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); Calc: group

Best regards
Comment 10 MD 2017-09-20 21:02:34 UTC
Not sure what you mean by "the only problem I still have is the pasting bit"

Is my description unclear?
Comment 11 Xavier Van Wijmeersch 2017-09-21 11:55:16 UTC
No, your description is clear
But when pressing ctlr+v for B300:B1 nothing happens, no results, only when saving and reopening the document then the results are correct. That's the problem I have.
Correct observation as for comment7 and 8

Best regards
Comment 12 Xisco Faulí 2017-09-22 07:49:53 UTC
You can't confirm your own bugs. Moving it back to UNCONFIRMED until someone else confirms it.
Comment 13 Jean-Baptiste Faure 2017-09-24 10:42:17 UTC
Not reproducible for me with 

Build ID: 1:5.4.1~rc2-0ubuntu0.16.04.1~lo0
CPU threads: 4; OS: Linux 4.4; UI render: default; VCL: gtk3; 
Locale: fr-FR (fr_FR.UTF-8); Calc: single

Build ID: 3766aa5b3232beba4e4989696b6d19103ba0d62f
Threads CPU : 4; OS : Linux 4.4; UI Render : par défaut; VCL : gtk3; 
Locale : fr-FR (fr_FR.UTF-8); Calc: single

Build ID: ae0edc4740ea95cd456eb7157bd048615de9212e
Threads CPU : 4; OS : Linux 4.4; UI Render : par défaut; VCL: gtk3; 
Locale : fr-FR (fr_FR.UTF-8); Calc: single

All these versions give #REF! in B1:B3 and 1 in B147:B153 after step 7.

Best regards. JBF
Comment 14 Buovjaga 2017-10-29 19:23:04 UTC
MD: per JBF's results, do you still see the wrong result in version 5.4.x?

Change back to UNCONFIRMED, if the problem persists. Change to RESOLVED WORKSFORME, if the problem went away.
Comment 15 QA Administrators 2018-05-02 15:46:30 UTC Comment hidden (obsolete)
Comment 16 QA Administrators 2018-05-30 16:47:21 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