Bug 58874 - OFFSET function in array context compatibility with Excel
Summary: OFFSET function in array context compatibility with Excel
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Eike Rathke
URL:
Whiteboard: target:6.0.0 target:6.1.0 target:6.0....
Keywords:
: 96595 106478 (view as bug list)
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2012-12-30 00:29 UTC by Giovanni
Modified: 2018-11-10 16:37 UTC (History)
9 users (show)

See Also:
Crash report or crash signature:


Attachments
contains the formula and some data (13.78 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2012-12-30 00:29 UTC, Giovanni
Details
Lo Calc test file (13.03 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-01-27 09:03 UTC, GerardF
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Giovanni 2012-12-30 00:29:17 UTC
Created attachment 72285 [details]
contains the formula and some data

starting from this link:
Source: Excel Count Functions -- Count Excel Cells Address :
<http://www.contextures.com/xlFunctions04.html#Visible>

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A1:A10,ROW(A1:A10)-MIN(ROW(A1:A10)),,1)), --
(A1:A10=A12))

I applied it on a document: once opened in libreoffice the formula gives an error while in excel it does work fine.

=MATR.SOMMA.PRODOTTO(SUBTOTALE(3;SCARTO($G$9:$G$260;RIF.RIGA($G$9:$G$260)-MIN(RIF.RIGA($G$9:$G$260));;1)); --($G$9:$G$260=H4))

I attach the small sample file
Comment 1 Giovanni 2012-12-30 00:31:28 UTC
the formula has been tested both in libreoffice and apache openoffice, no way

I just wonder whether there is a more simple function to get what I need, to count precise values in a filtered list
Comment 2 A (Andy) 2013-03-29 14:13:56 UTC
reproducible with LO 4.0.1.2 (Win7 Home, 64bit), marked as bug

@Markus: Could this issue maybe be something for you?
Comment 3 GerardF 2013-11-18 08:48:23 UTC
With Excel SUBTOTAL(9;OFFSET(A1;{1;3;4};0)) returns an array of SUBTOTAL results :
{SUBTOTAL(9;A2);SUBTOTAL(9;A4);SUBTOTAL(9;A5)}
With LibO/AOO the same formula returns a scalar which is the result of :
SUBTOTAL(9;{A2;A4;A5})

So SUBTOTAL in Excel treats the result of OFFSET as 3 ranges of 1 cell while in LibO this is a range of 3 cells.

I don't know where is the truth :)
Comment 4 GerardF 2013-11-18 09:07:29 UTC
A workaround for your sample :
Add a column with the formula =SUBTOTAL(3;A9) in H9 and drag down (with filter to "All")
Then =SUMPRODUCT($H$9:$H$260; --($G$9:$G$260=H4)) will gives you the result with a simpliest and shorter formula.
You need 3.6.7/4.0.4/4.1.0 minimum, see https://bugs.freedesktop.org/show_bug.cgi?id=64384
Comment 5 QA Administrators 2015-04-19 03:19:54 UTC Comment hidden (obsolete)
Comment 6 Winfried Donkers 2015-05-20 06:37:12 UTC
Behaviour in Calc with version 4.4.3.2 is still as mentioned in Description.

The behaviour of SUBOTAL in Calc seems to be compliant with the Open Document Format - OpenFormula.
That does not mean that Excel is to blame, but that work may be needed to make Excel and Calc compatible.
I've added it to my list to address when I find time.
Comment 7 GerardF 2016-01-26 14:05:22 UTC
*** Bug 96595 has been marked as a duplicate of this bug. ***
Comment 8 GerardF 2016-01-26 14:18:34 UTC
On AskLibO, Eike wrote : "Apparently Excel treats an array of OFFSET() results differently if it occurs as an argument to SUBTOTAL()."

It is not only when OFFSET is inside SUBTOTAL.
=OFFSET(A1;ROW(1:4);0)
result in an array of 4 cells (A1:A4) in Calc
result in 4 arrays of 1 cell in Excel.

I will attach a sample when having more time.
Comment 9 GerardF 2016-01-27 09:03:50 UTC
Created attachment 122234 [details]
Lo Calc test file

The expected file.
Comment 10 QA Administrators 2017-03-06 13:44:51 UTC Comment hidden (obsolete)
Comment 11 Eike Rathke 2017-03-14 22:18:26 UTC
(In reply to GerardF from comment #8)
> It is not only when OFFSET is inside SUBTOTAL.
> =OFFSET(A1;ROW(1:4);0)
> result in an array of 4 cells (A1:A4) in Calc
> result in 4 arrays of 1 cell in Excel.
Thanks for analysing and the example. I'm not sure that's all there is to it, but it may explain why in Excel it's not possible to enter {=OFFSET(A1,{2,4},0)} as a two-cells array formula (results are all #VALUE!), whereas Calc has no problem with.

Currently there are no means to return (and further process) a vector of arrays, each one element in this example but {=OFFSET(A1:A3,{2,4},0)} probably should return 2 arrays of 3 rows each (for A3:A5 and A5:A7), which actually already almost happens, the final Calc result is a 2 columns x 3 rows matrix, but it gets a little weird with {=OFFSET(A1:B3,{2,4},0)} ...

Squeezing such "vector of arrays" (if that really is what there is to it) into the existing interpreter structures would be quite challenging..

However, in Excel the support of that seems to be very special cased, for example with A1:A5={1;2;4;8;16} the expression =SUMPRODUCT(OFFSET(A1,{2,4},0)) (or =SUMPRODUCT(OFFSET(A1,{2;4},0)) for {2;4} column vector just in case it mattered) simply does not work and returns 0, not even an error, within or without array context. Also =SUM(OFFSET(A1,{2,4},0)) does not yield the expected result of 20 but 4 instead, entered as array formula it returns the array {4,16}, so treating the two arrays as independent, both results the same as with =SUBTOTAL(9,OFFSET(A1,{2,4},0)). To me this is all a mess and I'm not sure if we aren't just chasing a corner case of undefined behaviour and implementation detail. It may make sense for SUBTOTAL to treat the array results independently, but otherwise?

Btw, Gnumeric for those examples
a) =SUMPRODUCT(OFFSET(A1,{2,4},0)) gives 4
b) {=SUMPRODUCT(OFFSET(A1,{2,4},0))} gives 0
c) =SUBTOTAL(9,OFFSET(A1,{2,4},0)) gives 4
d) {=SUBTOTAL(9,OFFSET(A1,{2,4},0))} gives 20
e) =SUM(OFFSET(A1,{2,4},0)) gives 4
f) {=SUM(OFFSET(A1,{2,4},0))} gives 20

Hooray.

Calc gives 20 for a) and b), which to me is the only logically correct result, and c)-f) are identical with Gnumeric.
Comment 12 Eike Rathke 2017-05-19 16:17:46 UTC
So indeed, for OFFSET in array context Excel returns an array of references, e.g. OFFSET(A1,ROW(1:3),0,2) returns
A2:A3
A3:A4
A4:A5
which then fed to SUBTOTAL calculates each independently and returns an array of results.

With A1:A5={1;2;4;8;16} and =SUBTOTAL(9,OFFSET(A1,ROW(1:3),0,2)) the result is {6;12;24}

Same for =SUM(OFFSET(A1,ROW(1:3),0,2)) in array context.
I assume the same works for all functions that can be specified as SUBTOTAL first argument, at least it does for AVERAGE as well.

(Note: {1;2;3} should be the same as ROW(1:3) but Excel does not accept the inline array.)
Comment 13 Commit Notification 2017-05-22 09:06:15 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=6a569fd47e3665b0aaec397ace0346938bb3b4bf

Introduce ScJumpMatrix::mvRefList for array of reference results, tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 14 Commit Notification 2017-05-22 09:06:51 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=3a3064fcb4778b050398d98598d6ed61e8298134

Introduce ScRefListToken::mbArrayResult for reference results, tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 15 Commit Notification 2017-05-22 09:08:25 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=b086633566cbcc5e73add5d195ce0ab207ea8f7c

Create RefList at JumpMatrix if current function returns Reference, tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 16 Commit Notification 2017-05-22 09:09:03 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=b45a7ed1e687f7c54f16f6ab65b85ab3d945af68

Do not force return value to double in SUBTOTAL and AGGREGATE, tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 17 Commit Notification 2017-05-22 09:09:39 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=f6703ce85f9399b94958601ed6623865b205c5fb

Change IterateParameters to push token instead of returning double, tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 18 Commit Notification 2017-05-22 09:10:14 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=91bdd41bb0092e7ed9ef4e5b782650be3cfd9440

Handle reference list as array in IterateParameters(), tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 19 Commit Notification 2017-05-22 09:10:50 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=1e70dedee1c7be912e4aaa2ff12b046dbd364a86

Add unit test for SUBTOTAL over reference list in array context, tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 20 Commit Notification 2017-05-22 09:11:26 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=17dd13922c01a864e5124afc97c9b19f0e29b44c

Factor out to ScInterpreter::GetRefListArrayMaxSize(), tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 21 Commit Notification 2017-05-22 09:12:02 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=5236e847191bbc3fb47a630bec5dbe8ed65679d7

const nMatRows, tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 22 Commit Notification 2017-05-22 09:12:39 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=c41b82203ef54a74bd0693e29f65617d0e158a06

Handle MIN and MAX with arrays of references, tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 23 Commit Notification 2017-05-22 09:13:14 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=667e0625090f084b8d9ae5a885b6b4624766ed6c

Unit test for SUBTOTAL MIN,MAX with array of references, tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 24 Commit Notification 2017-05-22 09:13:49 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=f6cdba8a801ba296bd3f7ca6334fc3671b0bbe58

Factor out ScInterpreter::SwitchToArrayRefList(), tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 25 Commit Notification 2017-05-22 11:01:59 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=24b577127d16810a809b19e7ec869509fae8b901

Generalize to lambda MatOpFunc(), tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 26 Commit Notification 2017-05-22 12:10:25 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=61af27e2b8041aac756b2f49cc2c31e5578a638b

Make GetStVarParams() parameter bTextAsZero non-optional, tdf#58874 prep

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 27 Commit Notification 2017-05-22 12:49:06 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=2ab771e0acd54ee164075f71fc4701f7b7cc3d06

Move result token push into GetStVarParams(), tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 28 Commit Notification 2017-05-22 14:26:30 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=44b56bab060b3268617ed930fb783a20e617d137

Get vector count right, tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 29 Commit Notification 2017-05-22 14:27:06 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=3e9f9bff4a3d8365f58545dae0c004a1254a56e3

Unit test for SUBTOTAL AVERAGE with array of references, tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 30 Commit Notification 2017-05-22 15:00:42 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=fda09a666dd22aa6b2a9abfb25f7c97a3ab910fe

Handle STDEV, STDEVP, VAR and VARP with arrays of references, tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 31 Commit Notification 2017-05-22 15:01:17 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=a21f1c11aacde31fa190859828f257544afea818

Unit test for SUBTOTAL STDEV with array of references, tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 32 Eike Rathke 2017-05-22 15:07:48 UTC
Note: the commit notification has a hickup, all commits on master are NOT for 5.4.0 but for the next release (5.5). If I can finish this in time and good shape I might backport to 5.4.0
Comment 33 Commit Notification 2017-05-22 16:03:43 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=e8a742344d612067a7a0ba5c752176d12a559967

Unit tests for ...,ref1,arrayofrefs,ref2) to result array, tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 34 Commit Notification 2017-05-22 19:18:30 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=8b55956d6a07d1d6b62ffdf58277e6752513f4c0

Introduce GetMatrix() from svRefList, tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 35 Commit Notification 2017-05-22 19:19:06 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=6b417ec3daaec72c736fcfe0d980ce205e589633

Handle SUMPRODUCT with svRefList arguments, tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 36 Commit Notification 2017-05-22 19:49:13 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=c31bed5cd54e3db5a22d15d794d326ce9cafe146

svRefList argument can be handled now in ForceArray context, tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 37 Commit Notification 2017-05-22 20:14:59 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=70dd07cf82746362f621fb6510239e2c0034aa5f

Revert "Handle SUMPRODUCT with svRefList arguments, tdf#58874" (Excel can't)

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 38 Commit Notification 2017-05-23 08:44:31 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=b9ecc28533ed366bc6544303df763b6be29c2963

Handle COUNTIF with array of references, tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 39 Commit Notification 2017-05-23 08:45:08 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=44a1f0c17d3c5b212ff60b2f24f50693b3da9eab

Move pushing result token into IterateParametersIf(), tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 40 Commit Notification 2017-05-23 08:45:44 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=6a6aa56b45f19c250e9c4111b478203ddb75478b

Handle SUMIF and AVERAGEIF with array of references, tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 41 Eike Rathke 2017-05-23 09:56:41 UTC
*** Bug 106478 has been marked as a duplicate of this bug. ***
Comment 42 Commit Notification 2017-05-23 11:17:43 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=30660bd7e9d7f8639508dfceb24c335388370b0b

MustHaveParamCount() already pushes error, tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 43 Commit Notification 2017-05-23 11:34:53 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=5e974c89c8647040f796605aeae1322303532e58

Handle COUNTBLANK with array of references, tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 44 Commit Notification 2017-05-23 11:43:53 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=28ff25934ca1442a4a7f22f0d915ed7b782ca379

Unit test for COUNTBLANK with array of references, tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 45 Commit Notification 2017-05-23 12:54:59 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=d24a9a7513805f1baee3ff7c9b8a25eb467a0f6b

Move pushing the result token into IterateParametersIfs, tdf#58874

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 46 Commit Notification 2017-06-01 16:02:12 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=e4f2aab11721cc6842c2c821ff9dacebc6095a56

Perf: do not calculate a null-operation with the result matrix, tdf#58874

It will be available in 5.5.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 47 Commit Notification 2017-06-01 17:47:44 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=41ba5f649f361bfa6965a7c8f2e2ffa084317c45

Perf: do not calculate a null-operation with the result matrix, tdf#58874

It will be available in 5.5.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 48 Commit Notification 2017-06-08 12:57:16 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=a5c1b12bc249660febbc5752bebd449f27af9e72

Redundant nColSize,nRowSize, use nDimensionCols,nDimensionRows, tdf#58874 prep

It will be available in 5.5.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 49 Commit Notification 2017-06-09 22:46:57 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=c47fc935a135b4728b452d6f94a856040552a90c

COUNTIFS, SUMIFS, AVERAGEIFS, MINIFS, MAXIFS with reference arrays, tdf#58874

It will be available in 5.5.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 50 Commit Notification 2017-06-09 22:47:30 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=076ed447f694239d5c67adee528ea6e471d909ff

Unit test for COUNTIFS, SUMIFS, AVERAGEIFS with array of references, tdf#58874

It will be available in 5.5.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 51 Eike Rathke 2017-06-13 11:11:38 UTC
(In reply to Eike Rathke from comment #11)
> example with A1:A5={1;2;4;8;16}
> [... Gnumeric ...]
> a) =SUMPRODUCT(OFFSET(A1,{2,4},0)) gives 4
> b) {=SUMPRODUCT(OFFSET(A1,{2,4},0))} gives 0
> [...]
> Calc gives 20 for a) and b), which to me is the only logically correct
> result

That was a thinko, the 4 and 16 would not be within one array (and then summed), but two individual arrays of each one cell that are multiplied so the logically correct result would be 64. Which neither Excel nor Gnumeric deliver, but Calc will..
Comment 52 Commit Notification 2017-06-13 11:37:47 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=8f187d38b18f65ed70a225f63869147605fda704

Handle SUMPRODUCT with reference list and array of references, tdf#58874

It will be available in 6.0.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 53 Commit Notification 2017-06-13 14:48:49 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=f9cf614e7ea73e47a71cd2c0d1d53af9a6e48984

Final result of svRefList can be an array in some cases, tdf#58874 related

It will be available in 6.0.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 54 Commit Notification 2017-06-14 09:42:33 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=90bea1ea24838845646ec253d1a4b472b73d53ca

Final result of Reference array is svMatrix instead of svRefList, tdf#58874

It will be available in 6.0.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 55 Xisco Faulí 2017-07-17 08:31:01 UTC
Polite ping: is this bug fixed? if so, please close it as RESOLVED FIXED
Comment 56 Eike Rathke 2017-09-04 17:42:54 UTC
I'm not sure yet. There may be more functions where Excel evaluates an array of references differently.
Comment 57 GerardF 2017-09-05 08:46:33 UTC
(In reply to Eike Rathke from comment #56)
> I'm not sure yet. There may be more functions where Excel evaluates an array
> of references differently.

As far as I can remember (I don't use Excel for about 8 years), (V/H)LOOKUP produce a vector of unique cell array like SUBTOTAL does.
Excel can't handle SUMPRODUCT(VLOOKUP(SearchCriterion_Array; Array; Index; Sorted)), LO can.
But I'm not sure it will be an improvement to micmics XL...
Comment 58 Commit Notification 2018-02-08 23:20:24 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=e0a94ded5d1635fa2a2d9e222bfcfa0a2289a01f

Resolves: tdf#115493 use matrix as result of jump command, tdf#58874 related

It will be available in 6.1.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 59 Eike Rathke 2018-02-08 23:29:00 UTC
Let's close this.
Comment 60 Commit Notification 2018-02-14 15:34:04 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=b05e4eab225a536c3e5bdae2ca991ee9f53ad1a6

Unit test tdf#115493 use matrix as result of jump command, tdf#58874 related

It will be available in 6.1.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 61 Commit Notification 2018-02-26 10:43:56 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=ed2a66a2ccace13c3d85490300cd623109214b0e&h=libreoffice-6-0

Resolves: tdf#115493 use matrix as result of jump command, tdf#58874 related

It will be available in 6.0.2.

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

Affected users are encouraged to test the fix and report feedback.
Comment 62 Commit Notification 2018-03-06 18:00:24 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=cfc6cf5177f8df23af35c4509c0276a19de56cce

Resolves: tdf#116215 fewer array of references cases, tdf#58874 related

It will be available in 6.1.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 63 Eike Rathke 2018-03-06 18:39:45 UTC
Actually that last ^^^ commit was for bug 116100, not 116215.
Comment 64 Commit Notification 2018-03-21 11:29:47 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=b52ca1a7cf9e3652ebd433753b6642b6f5124d1f&h=libreoffice-6-0

Resolves: tdf#116100 fewer array of references cases, tdf#58874 related

It will be available in 6.0.4.

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

Affected users are encouraged to test the fix and report feedback.
Comment 65 Commit Notification 2018-03-28 21:54:58 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-0-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=3f0705b5037fefec43fcf234127b3d1fd84f846f&h=libreoffice-6-0-3

Resolves: tdf#116100 fewer array of references cases, tdf#58874 related

It will be available in 6.0.3.

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

Affected users are encouraged to test the fix and report feedback.
Comment 66 Commit Notification 2018-11-09 18:15:49 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/6ce84da750abcec3139bf12bc212fb2f03460add%5E%21

Resolves: tdf#120895 new ParamClass::ReferenceOrRefArray, tdf#58874 related

It will be available in 6.2.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 67 Commit Notification 2018-11-10 16:37:31 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/dc134874b62422729a6cbe62e48639e721eacfdf%5E%21

Unit test for array of references in ForceArray, tdf#120895, tdf#58874

It will be available in 6.2.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.