Description: The following function becomes an error in Calc. =MAX(FREQUENCY(ROW($L$11:$L$1000),($L$11:$L$1000>=0)*ROW($L$11:$L$1000))-1) The error message is #VALUE !. The file does not get an error in EXCELL. If you read an error in Calc with EXCELL, save it, and read the file in Calc, you will not get an error. If you copy and paste the function on the file where no error occurs, you will get an error again. Actual Results: It will happen every time Expected Results: I get an error Reproducible: Always User Profile Reset: No Additional Info:
Hello, Thank you for filing the bug. Please send us a sample document, as this makes it easier for us to verify the bug. I have set the bug's status to 'NEEDINFO', so please do change it back to 'UNCONFIRMED' once you have attached a document. (Please note that the attachment will be public, remove any sensitive information before attaching it.) How can I eliminate confidential data from a sample document? https://wiki.documentfoundation.org/QA/FAQ#How_can_I_eliminate_confidential_data_from_a_sample_document.3F Thank you
Created attachment 147799 [details] The location of the bug is P7 - S7.
OK, I see. With LO Calc, the formula need to be validated as an array formula. Excel seems to have "forced array parameters" for FREQUENCY|MAX (no need CSE) @Eike ?
Created attachment 147843 [details] Comparison LibreOffice 6.3 Master and MSO 2010
Also reproduced in Version 4.1.0.0.alpha0+ (Build ID: efca6f15609322f62a35619619a6d5fe5c9bd5a) In LibreOffice 3.3.0 OOO330m19 (Build:6) tag libreoffice-3.3.0.4 P7 and Q7 have #VALUE as well...
Odd. The Microsoft documentation for FREQUENCY() says "Because FREQUENCY returns an array, it must be entered as an array formula." https://support.office.com/en-US/article/FREQUENCY-function-44E3BE2B-ECA0-42CD-A3F7-FD9EA898FDB9 Which on the other hand is misleading, because if just a data range and a bins range is given and not in array mode the function returns an array nevertheless, but they probably mean that the function is to be entered as array formula across a selected cell range to display the array and not just the first value. What's problematic here is the $L$11:$L$1000>=0 subexpression, which in this context with a range works only in array mode and otherwise doesn't result in an implicit intersection when given on row 7, hence the #VALUE! error; also ROW() not in array mode returns only the first row of a range, not an array. ECMA-376-1:2016 OOXML 18.17.7.127 FREQUENCY says "A call to FREQUENCY shall be an array formula." but on the other hand doesn't say anything like that about 18.17.7.309 SUMPRODUCT which definitely forces array mode on its arguments, or any other function known to force array mode. We may have to submit an update to ODFF as well that doesn't state the ForceArray attribute https://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#FREQUENCY
(In reply to Eike Rathke from comment #6) > ECMA-376-1:2016 OOXML 18.17.7.127 FREQUENCY > says "A call to FREQUENCY shall be an array formula." Maybe even wants to say that the entire formula expression is to be forced to array mode, not just the parameters to FREQUENCY(). Don't have an adhoc example at hand how that could be checked and verified/falsified.
Ok, what does Excel produce for =SUM(-ABS(FREQUENCY({1,1,-1},{0,1}))) If it forces array mode on the entire formula the result should be -3 If it does not force array mode the result should be -1 Explanation: The FREQUENCY result here is an array {1,2,0}. ABS() in non-array mode takes one (the first) scalar value and returns 1, which negated is -1, of which the SUM() is -1. ABS() in array mode iterates over the array and returns {1,2,0}, which negated is {-1,-2,0}, of which the SUM() is -3. That would be the same as if the formula was entered in array mode. Could someone with access to Excel please check? Thanks.
(In reply to Eike Rathke from comment #8) > Ok, what does Excel produce for > > =SUM(-ABS(FREQUENCY({1,1,-1},{0,1}))) > > If it forces array mode on the entire formula the result should be -3 > If it does not force array mode the result should be -1 > > Explanation: > The FREQUENCY result here is an array {1,2,0}. > ABS() in non-array mode takes one (the first) scalar value and returns 1, > which negated is -1, of which the SUM() is -1. > ABS() in array mode iterates over the array and returns {1,2,0}, which > negated is {-1,-2,0}, of which the SUM() is -3. That would be the same as if > the formula was entered in array mode. > > Could someone with access to Excel please check? Thanks. Result in Office 365 is -3
Thanks! More work to do..
@GerardF: Umm.. to check whether my assumption about the "array in non-array mode" actually holds, this =SUM(-ABS({1,2})) does produce -1, yes? If -1, one more please to check whether array mode is indeed forced onto the entire formula or just to the expression part that contains the FREQUENCY() call: =SUM(-ABS(FREQUENCY({1,1,-1},{0,1})))+SUM(-ABS({1,2})) Should produce -6 if the entire formula in array mode (which I'd assume), or -4 if only the subexpression containing FREQUENCY() is in array mode.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/+/a6572f5f014ed159e340ed31b6e5a08527c09088%5E%21 Resolves: tdf#122301 ReferenceOrForceArray for FREQUENCY() parameters It will be available in 6.3.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.
Created attachment 148115 [details] picture_excel_result First result is -3, second is -6. Array constant are treated as array without CSE while cell range are not. See attached picture (French XL version).
Created attachment 148124 [details] Results of formulas from comment 8 and comment 11 using cell ranges instead of literal arrays
(In reply to GerardF from comment #13) > First result is -3, second is -6. > Array constant are treated as array without CSE while cell range are not. Sigh.. ok, let's forget that path for now, thanks. (In reply to Mike Kaganski from comment #14) > Results of formulas from comment 8 and comment 11 using cell ranges instead > of literal arrays A3 is -5 ??!? So that places the first SUM(-ABS(...)) into array mode and the second SUM(-ABS(E2:E3)) not which then takes the intersection E3? If so, moving the same formula to a different position should change result, and for example on A4 it should be #VALUE! because there is no intersection with E2:E3.
(In reply to Eike Rathke from comment #15) > A3 is -5 ??!? So that places the first SUM(-ABS(...)) into array mode and > the second SUM(-ABS(E2:E3)) not which then takes the intersection E3? If so, > moving the same formula to a different position should change result, and > for example on A4 it should be #VALUE! because there is no intersection with > E2:E3. True, as seen on screenshot attachment 148115 [details] from comment 13 :-( a mess, isn't it?
Created attachment 148245 [details] What does Excel produce in A9:A13 here?
(In reply to Eike Rathke from comment #17) 1 #VALUE! #VALUE! #VALUE! #VALUE!
(In reply to Eike Rathke from comment #17) > Created attachment 148245 [details] > What does Excel produce in A9:A13 here? I get 7 #VALUE! #VALUE! #VALUE! #VALUE! using Microsoft Office 365, Version 1812 (Build 11126.20196 Click-to-Run)
(In reply to Regina Henschel from comment #19) Regina is absolutely correct - yes, that's 7 - sorry for not checking that the file opened "locked", and unlocking was needed to recalculate. (It was 2 am local time :-))
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/+/d0ded163d8e93dc5b10d7a7c9bdab1d0a6a50bac%5E%21 Related: tdf#122301 FREQUENCY() with ForceArrayReturn on caller It will be available in 6.3.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.
Pending review https://gerrit.libreoffice.org/66487 for 6-2
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-6-2": https://git.libreoffice.org/core/+/cb40558531c35b89510f0276bc78d9db4a76a9f3%5E%21 Resolves: tdf#122301 FREQUENCY() ReferenceOrForceArray, ForceArrayReturn It will be available in 6.2.1. 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/+/d8886a051abcb5abf52038bf3e5bced9f10f28af%5E%21 Add tests for FREQUENCY() ReferenceOrForceArray, ForceArrayReturn, tdf#122301 It will be available in 6.3.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.
Fixed on Version: 6.3.0.0.alpha0+ Build ID: 3424004cca7cb61043800f0ff0acc9de64768276 CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; TinderBox: Linux-rpm_deb-x86_64@86-TDF, Branch:master, Time: 2019-01-26_00:45:09 Locale: ro-RO (ro_RO.UTF-8); UI-Language: en-US Calc: threaded On P7:S7 I have 6 8 6 8, so it's ok now.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/+/5413c8871dec08eff19f514f5f391b946a45c86c%5E%21 Related: tdf#122301 TRANSPOSE() with ForceArrayReturn on caller It will be available in 6.3.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 "libreoffice-6-2": https://git.libreoffice.org/core/+/e0baddc00570692e09834f165b4e1913575fc338%5E%21 Related: tdf#122301 TRANSPOSE() with ForceArrayReturn on caller It will be available in 6.2.3. 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.