Bug 151028 - Sparkline cell reference formula erroneous
Summary: Sparkline cell reference formula erroneous
Status: RESOLVED DUPLICATE of bug 151027
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All Linux (All)
: medium normal
Assignee: Not Assigned
Depends on:
Reported: 2022-09-17 13:48 UTC by aplatypus
Modified: 2023-02-16 17:11 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:

Incorrect formula screenshot: CALC.Erroneous-reference-formula.png (32.16 KB, image/png)
2022-09-17 13:53 UTC, aplatypus
Only form that works, sheet name is forced (unconventional): CALC.Absolute-reference-formula.png (19.20 KB, image/png)
2022-09-17 14:07 UTC, aplatypus

Note You need to log in before you can comment on or make changes to this bug.
Description aplatypus 2022-09-17 13:48:50 UTC
* I like the fact that we now have Sparklines in Calc.
* I have attached two screenshots
   1. CALC.Erroneous-reference-formula.png
   2. CALC.Absolute-reference-formula.png

* In image #1, you can see the forula has data for the Sparkline from column 20 to column23
* The actual data cells are (absolute): R75C21:R75C24
* The cell reference rendered is incorrect.  Internally it must be correct since the graph is accuate. 
* I have checked several Sparkline instances the graph is correct but the cell reference is erroneous.  Also If I switch to VisiCalc reference the cells are correctly U76:X76.

Steps to Reproduce:
1. Select Multiplan/Excel reference type: row and column for cell references.
2. Put numeric values in 4 or more cells along a row
3. Insert a Sparkline using these values as source
4. Check the formula displayed -- You will see that the formula reference is out by one column

Actual Results:
* Formula reference is one column to the left of the correct range.
* The formula also includes the Worksheet name where it is NOT required
* The formular unlike All other default reference behaviour uses fixed absolute references, not relative references.
* Row/Column reference syntax is incorect.  
  x The syntax: R[76] means Current row plus 76 or R[+76]
  x The absolute reference for row 76 is correctly: R76
  x Whereas, U76 is anabsolute reference to Row 76
* Sparkline formula can not be effectively copied to other cells because the formulae are fixed to a sheet and use fixed cells.

Expected Results:
* Row/Column formula references must be correct
* Standard formula conventions must be adhered to
* Relative referinces are the usual default
* Worksheet names are only used when the target is on another sheet
* Formula must be suitable for copying and use in other locations
* All valid formula forms should be allowed.  I was not able to use a list or cells.  Only an absolute contiguoous range was accepted.  
  x This is not the usual case.
  x I see no reason why other suitable forms like a list is invalid(??).

Reproducible: Always

User Profile Reset: No

OpenGL enabled: Yes

Additional Info:
Version: / LibreOffice Community
Build ID: 3c58a8f3a960df8bc8fd77b461821e42c061c5f0
CPU threads: 16; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-AU (en_AU.UTF-8); UI: en-US
Calc: threaded
Comment 1 aplatypus 2022-09-17 13:53:27 UTC
Created attachment 182522 [details]
Incorrect formula screenshot: CALC.Erroneous-reference-formula.png

Correct (relative) reference: RC[+6]:RC[+9]
Corresponding absolute range should be: R75C21:R75C24
Comment 2 aplatypus 2022-09-17 14:07:12 UTC
Created attachment 182524 [details]
Only form that works, sheet name is forced (unconventional): CALC.Absolute-reference-formula.png

* The only way to get a Sparkline to work/display must submit data this way:

1. Wants sheet name (not normal Calc behaviour)
2. Formula in absolute reference  (not normal Calc behaviour)
3. Wants cells to be a contiguous range  (not normal Calc behaviour)
4. Will not accept a list of cells eg.; (I55, I62, I69, I76)
5. No error or log messages are shown for 'unacceptable', yet valid and reasonable formulae
Comment 3 Buovjaga 2023-02-16 17:11:53 UTC

*** This bug has been marked as a duplicate of bug 151027 ***