Bug 128846 - Inconsistent behaviour of option "Expand References..."
Summary: Inconsistent behaviour of option "Expand References..."
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.0 all versions
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2019-11-16 23:01 UTC by Andreas Säger
Modified: 2019-11-18 18:12 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Spreadsheet with total and a calculated field of running totals. (20.42 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-11-16 23:03 UTC, Andreas Säger
Details
excel spreadsheet (9.01 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2019-11-17 08:42 UTC, Oliver Brinzing
Details
screenshots from aoo415 with expand referenced enabled (326.67 KB, application/pdf)
2019-11-17 16:40 UTC, Oliver Brinzing
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Andreas Säger 2019-11-16 23:01:31 UTC
The following used to be the correct behaviour since the beginning of OpenOffice.org:
WITHOUT option "Expand References"
-- any insertion at the first row of the referenced range shifts down the reference.
-- any insertion directly below the referenced just inserts cells _below_ the reference without affecting the reference.

WITH option "Expand References"
-- any insertion at the first row of the referenced range expands the reference.
-- any insertion directly below the referenced expands the reference.

In both cases any insertion between row #2 and the last row expands the reference.
###########################################################################
So far LibreOffice behaves consistently, nevertheless some flaw has been introduced in version 6, may be earlier.
OpenOffice used to preserve the formulas adjacent to the inserted cells whereas LibreOffice consequently expands all references which flaws some calculated fields such as running totals.
In the attached document, the last reference of the running total expands beyond the current row if "Expand References" is set.
Formula in B11 =SUM($A$6:A11) becomes =SUM($A6:A12) although the cell B11 does not move.
The not adjacent formula cell in B2 which calculates the overall =SUM($A$6:$A$11) expands to =SUM($A$6:$A$12) which is perfectly wanted behaviour. However, the adjacent calculated field should not change.
This becomes more apparent when you turn on the R1C1 formula view. The  entire calculated field is calculated as =SUM(R6C1:RC[-1]) describing the sum from absolute Row6,Col1 until THIS row, previous column. 
After inserting a new row, the last formula changes to  =SUM(R6C1:R[1]C[-1]) describing the sum from absolute Row6,Col1 until NEXT row,previous column.
Comment 1 Andreas Säger 2019-11-16 23:03:40 UTC
Created attachment 155885 [details]
Spreadsheet with total and a calculated field of running totals.
Comment 2 Oliver Brinzing 2019-11-17 08:42:59 UTC
Created attachment 155888 [details]
excel spreadsheet

(In reply to Andreas Säger from comment #0)

> The not adjacent formula cell in B2 which calculates the overall
> =SUM($A$6:$A$11) expands to =SUM($A$6:$A$12) which is perfectly wanted 
> behaviour. 

correct: =SUM($B$6:$B$11) expands to =SUM($B$6:$B$12)

> Formula in B11 =SUM($A$6:A11) becomes =SUM($A6:A12) although the cell B11
> does not move. However, the adjacent calculated field should not change.

with AOO 4.1.5 and "[x] expand references":
- select row 12
- context menu: insert row
-> formula in B11 changes from =SUM($A$6:A11) to =SUM($A$6:A12)

i cannot see a difference?

btw: excel suggest using: =SUM(INDIRECT("A6:A"&ROW()))
Comment 3 Andreas Säger 2019-11-17 15:07:29 UTC
> with AOO 4.1.5 and "[x] expand references":
> - select row 12
> - context menu: insert row
> -> formula in B11 changes from =SUM($A$6:A11) to =SUM($A$6:A12)
> 

The formula does not calculate the running total anymore. The meaning of this single formula in B11 has changed. It does not sum up the other column until the same row.
The R1C1 notation shows this more clearly.

> btw: excel suggest using: =SUM(INDIRECT("A6:A"&ROW()))

https://support.office.com/en-us/article/Calculate-a-running-total-in-Excel-1359BF89-180B-4771-B5B4-C6F6558549C5 describes the proceeding with normal formulas, cell insertion and formula copy.

I have to assume that Excel does not modify the existing formula because this would mix wrong formulas into the calculated field. 
And OpenOffice still shows the correct behaviour where certain references do NOT expand. At the moment I have difficulties describing a rule for this. The old source code should explain it better than I can right now. It could be that references adjacent to the inserted cells and in the opposite direction to the move direction remain untouched but I'm not sure.
Comment 4 Oliver Brinzing 2019-11-17 16:40:42 UTC
Created attachment 155898 [details]
screenshots from aoo415 with expand referenced enabled
Comment 5 Andreas Säger 2019-11-17 21:38:03 UTC
Please mark this as invalid or enhancement request? I don't know. 
Totally confused sincerely yours,
Andreas
Comment 6 Oliver Brinzing 2019-11-18 18:12:04 UTC
(In reply to Andreas Säger from comment #5)
> Please mark this as invalid or enhancement request? I don't know. 
> Totally confused sincerely yours,
> Andreas

ok, setting to WFM
I don't think, it's possible to distinguish between formulas which should expand or not expand if "expand references" is enabled.