https://bugs.documentfoundation.org/show_bug.cgi?id=172600
Bug ID: 172600
Summary: EDITING: Copy/pasting formula with relative references
in same row fails to adjust references to new location
Product: LibreOffice
Version: 26.2.1.2 release
Hardware: x86-64 (AMD64)
OS: Windows (All)
Status: UNCONFIRMED
Severity: minor
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
Description:
Copied a block of three partial rows (F314:J316) to (F332:J334) containing
cells with a mixture of pure numbers, references to single cells outside the
block (eg =f298) in f314, formulas with some references outside the block (eg
=f298+f315 in f316), and formulas with all references in the same row (eg
=if(e315=0,0,(h315-f315)/e315) in g315, or =if(e316=0,0,h316-f316/e316) in
g316).
The formulas copied from row 315 to row 333 kept the references as *315, while
the formulas copied from the other rows adjusted the references to *332 and
*334, as expected. The only difference I can see is that the cells in 315
copied to 333 had the values of just 0 in columns e, f, h, and i while the
others had formulas computed from other rows in those columns. The same
behavior applied to the formulas in column i, of the form
=if(h###=0,0,j###/h###).
Would expressing those zeros as =0 rather than 0 make a difference? These zero
cells are intended to be filled in as income arrives, and will NOT be copied
and pasted after those values are filled in with specific numbers.
Steps to Reproduce:
1.Build a spreadsheet with similar formulas and data, including a column e.
There should be at least two rows above the first of the three rows, the first,
representing previous YTD values, containing anything (comments?) in columns
A-D (in my case, they are fused into a single cell in every row), and nonzero
values in column e-j; the second, representing hours this period, containing a
single nonzero value in e; the three test case rows, described below, at least
one separator row; and three empty rows to move the test rows to.
>e3:i3, computed net and gross given previous YTD information:
=e2 =f1 =g1 =e3*g3+f3 =i1 =h3*i3
>e4:i4, computed hourly rate and net/gross ratin this pay period:
0 0 =if(e4=0,0,(h4-f4)/e4) 0 =if(h4=0,0,i4/h4) 0
or try substituting =0 for 0
>e5:i5, new YTD values:
=e1+e4 =f1+=f4 =if(e5=0,0,(h5-f5)/e5) =h1+h4 =if(h5=-,-,i5/h5) =h1+i4
2.Columns a-d (fused) = comments,= e = hours, f = non-hrly pay, g = average
hourly rate (given or computed), h = gross pay (given or computed),
i = net/gross ratio (given or computed), j = net pay (given or
computed)
3. copy/paste f3:j5 into new rows for various combinations of values in row 4.
Actual Results:
The equivalents of the formulas in g4 and I4, with zero values "0" not "=0" in
the other cells of e4:j4, keep the same row numbers, as if the cells had been
coded $col$row. When there are nonzero values in "row 4" cells containing the
inputs, the cell references adjust properly. Using "=0" rather than "0" fixes
the problem. the prefix "=" is not needed for nonzero values.
Expected Results:
Formulas stay formulas, with rows adjusted for the new location.
Reproducible: Always
User Profile Reset: No
Additional Info:
Version: 26.2.1.2 (X86_64)
Build ID: 620(Build:2)
CPU threads: 4; OS: Windows 10 X86_64 (build 19045); UI render: Skia/Raster;
VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL threaded
I am pressed for time, and do not want to lose my settings. If asked, I will
find time to do so.
--
You are receiving this mail because:
You are the assignee for the bug.
