https://bugs.documentfoundation.org/show_bug.cgi?id=172933
Bug ID: 172933
Summary: sparklines don't accept non-contiguous input ranges
and helper column workaround makes the sparkline
display zeros instead of gaps for empty cells
Product: LibreOffice
Version: 26.2.4.2 release
Hardware: x86-64 (AMD64)
OS: Windows (All)
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Chart
Assignee: [email protected]
Reporter: [email protected]
Description:
Sparklines do not seem to accept non-contiguous input ranges.
Important: Please also see the section "other information" below!
The issue is more complex than that.
Steps to Reproduce:
1. right-click cell > sparklines > edit sparkline
2. in the "data range" field enter "A1;C1;F1" or "A1,C1,F1"
3. click "OK"
4. repeat step 1
Actual Results:
The "data range" field reads: "#REF!"
Expected Results:
The "data range" field should read "A1;C1;F1"
Reproducible: Always
User Profile Reset: Yes
Additional Info:
Critically, the inelegant workaround with helper columns also fails:
Helper cells that reference an empty original data cell are displayed as zero
in the sparkline.
Example:
X1=A1
Y1=C1
Z1=F1
This creates a contiguous helper range for the non-contiguous original range.
However, when C1 is empty, then Y1=0 and displayed as zero in the sparkline.
# steps:
1. right-click on cell > sparklines > edit sparkline group ...
2. select: "type" = "line"
2. select: "display empty cells as" = "gap"
# actual result:
Y1 yields "0". Therefore the sparkline does not show a gap, but 0 value.
This means that gaps cannot be displayed for non-contiguous input ranges
(neither directly, since non-contiguous input ranges are unsupported in the
first place, nor indirectly since the helper columns show 0 for empty original
cells).
Instead of Y1=C1 I tried all of these alternatives:
Y1: =IF(C1<>""; C1; "")
Y1: =IF(C1<>""; C1; NA())
Y1: =IF(C1<>""; C1; {})
Y1: =IF(C1<>""; C1; 0/0)
No matter what I try here, the sparkline always displays the result as 0 value.
Bottom line:
This means that Calc sparklines cannot display gaps for empty cells in
non-contiguous input ranges.
MS Excel suffered from the same problem in 2013 as discussed here:
https://www.excelforum.com/showthread.php?t=935482
Excel sparklines displayed "" as 0.
However, Excel sparklines correctly treated NA() not as 0.
So unfortunately, LibreOffice Cals is lagging behind Excel by 13 years for that
particular issue.
# How to fix the problem ?
1. support non-contiguous input ranges.
2. display NA() as data gap, not as 0 zero value
side-note:
I am unable to reproduce related bug#159713
Sparklines *do* show gaps for me, for empty input cells.
But as explained above, if an empty input cell is translated to "0" in a helper
cell, the sparkline (logically) displays "0" instead of a gap. But there does
not seem to be a way to enter any formula in the helper cell that populates the
cell with a result that the sparkline will display as a gap, rather than 0
value.
--
You are receiving this mail because:
You are the assignee for the bug.
