Bug 151863 - Array SUM along sheets with INDIRECT reference list result and comparison operator returns #VALUE, while it works fine with 5.4.7
Summary: Array SUM along sheets with INDIRECT reference list result and comparison ope...
Status: CLOSED WONTFIX
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: target:7.5.0
Keywords: bibisectNotNeeded, regression
Depends on:
Blocks:
 
Reported: 2022-11-02 10:44 UTC by m_a_riosv
Modified: 2023-07-31 09:32 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Sum through sheets (11.75 KB, application/vnd.oasis.opendocument.spreadsheet)
2022-11-02 10:44 UTC, m_a_riosv
Details
Simplified file showing the issue (17.76 KB, application/vnd.oasis.opendocument.spreadsheet)
2022-11-02 20:36 UTC, m_a_riosv
Details

Note You need to log in before you can comment on or make changes to this bug.
Description m_a_riosv 2022-11-02 10:44:06 UTC
Created attachment 183373 [details]
Sum through sheets

With an array formula like:
{=SUM((INDIRECT("Week"&COLUMN(A$1:E$1)&".A"&ROW(A$1:A$500))=$A2)*INDIRECT("Week"&COLUMN(A$1:E$1)&".B"&ROW(A$1:A$500)))}
to sum along several sheets.

In the attached sample file, it works for me with:
Versión: 5.4.7.2
Id. de compilación: c838ef25c16710f8838b1faec480ebba495259d0
Subprocs. CPU: 4; SO: Windows 6.2; Repres. IU: GL; 
Configuración regional: es-AR (es_ES); Calc: CL

Fails with:
Versión: 6.4.7.2 (x64)
Id. de compilación: 639b8ac485750d5696d7590a72ef1b496725cfb5
Subprocs. CPU: 4; SO: Windows 10.0 Build 19045; Repres. IU: GL; VCL: win; 
Configuración regional: es-ES (es_ES); Idioma de IU: es-ES Calc: CL
Comment 1 Xisco Faulí 2022-11-02 11:28:06 UTC
I can reproduce it back to

Version: 6.0.0.0.alpha1+
Build ID: 6eeac3539ea4cac32d126c5e24141f262eb5a4d9
CPU threads: 8; OS: Linux 5.10; UI render: default; VCL: gtk3; 
Locale: es-ES (es_ES.UTF-8); Calc: group threaded

but not in

Version: 5.3.1.2
Build ID: e80a0e0fd1875e1696614d24c32df0f95f03deb2
CPU Threads: 8; OS Version: Linux 5.10; UI Render: default; VCL: gtk2; Layout Engine: new; 
Locale: de-DE (es_ES.UTF-8); Calc: group
Comment 2 Eike Rathke 2022-11-02 12:17:23 UTC
That is since the implementation for bug 58874 to create reference lists in OFFSET() and INDIRECT() (bug 106478) in array mode. Comparison operators like = can't handle those.

What results does Excel produce for the sample?
Comment 3 m_a_riosv 2022-11-02 20:36:39 UTC
Created attachment 183384 [details]
Simplified file showing the issue

Result with excel is #!REF! (Spanish)
Microsoft® Excel® para Microsoft 365 MSO (versión 2210 compilación 16.0.15726.20070) de 64 bits 

Like @eike has pointed, the issue is with the comparator.

A formula like: =SUM($Week1.B$1:$Week5.B$8), do the sum.
A formula like: {=SUM($Week1.B$1:$Week5.B$8=A2)}, gives an Err:504
Attached sample simplified file.
Comment 4 Eike Rathke 2022-11-02 22:35:59 UTC
(In reply to m.a.riosv from comment #3)
> Result with excel is #!REF! (Spanish)
Thus I tend to close this as a wontfix.

> Like @eike has pointed, the issue is with the comparator.
Not really.

The newly attached file is a different problem as it does not involve INDIRECT() and thus no reference list is created, though slightly related. The array formula {=SUM($Week1.B$1:$Week5.B$8=A2)} for the comparison (as for any operator/function that expects scalar values as operands) would have to create a 3D array of $Week1.B$1:$Week5.B$8 that simply is nowhere supported.
{=ABS($Week1.B$1:$Week5.B$8)} equally does not work.
Comment 5 m_a_riosv 2022-11-02 23:14:15 UTC
(In reply to Eike Rathke from comment #4)
> ...
> Thus I tend to close this as a wontfix.
>
 Then please do it.

Hope some day most of the functions can work 3D.
Comment 6 Eike Rathke 2022-11-04 00:01:25 UTC
I doubt 3D matrices will be generally supported; in many contexts it doesn't make sense, the result couldn't even be displayed on a 2D sheet.. only functions that accept multiple ranges like SUM() and its family would be able to consume 3D and deliver a meaningful result. All iterating over arrays for scalar values expecting functions would have to be adapted and wherever 2D matrices are handled as well, just for the little benefit of a few constellations that could make sense.
Comment 7 Commit Notification 2022-11-04 00:03:21 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/94c64c0c999eeaa91ebdc335186289046eacdc4f

Related: tdf#151863 Set #REF! error if reference list is not convertible

It will be available in 7.5.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 8 BogdanB 2023-07-28 20:36:38 UTC
Eike, is this bug solved? I have seen your commit, but I don't know if everything is now fine or not.
Comment 9 Eike Rathke 2023-07-31 09:32:22 UTC
Nothing solved, just the error code changed from #VALUE! to #REF!.

But yes, let's close this as won't fix.