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
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
reproducible with LO 4.0.1.2 (Win7 Home, 64bit), marked as bug @Markus: Could this issue maybe be something for you?
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 :)
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
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: *Test to see if the bug is still present on a currently supported version of LibreOffice (4.4.1 or later) https://www.libreoffice.org/download/ *If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior *If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System Please DO NOT *Update the version field *Reply via email (please reply directly on the bug tracker) *Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to "inherited from OOo"; 4b. If the bug was not present in 3.3 - add "regression" to keyword Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for your help! -- The LibreOffice QA Team This NEW Message was generated on: 2015-04-18
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.
*** Bug 96595 has been marked as a duplicate of this bug. ***
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.
Created attachment 122234 [details] Lo Calc test file The expected file.
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present on a currently supported version of LibreOffice (5.2.5 or 5.3.0 https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to "inherited from OOo"; 4b. If the bug was not present in 3.3 - add "regression" to keyword Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug-20170306
(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.
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.)
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
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.
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.
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.
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.
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.
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.
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.
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.
*** Bug 106478 has been marked as a duplicate of this bug. ***
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.
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.
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.
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.
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.
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.
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.
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.
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.
(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..
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.
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.
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.
Polite ping: is this bug fixed? if so, please close it as RESOLVED FIXED
I'm not sure yet. There may be more functions where Excel evaluates an array of references differently.
(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...
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.
Let's close this.
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.
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.
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.
Actually that last ^^^ commit was for bug 116100, not 116215.
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.
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.
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.
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.