Created attachment 156826 [details] Spreadsheet of bug Upon using the AGGREGATE function and selecting any of the options to ignore errors, Calc does not ignore DIV/0 errors.
Created attachment 156827 [details] Expected results
Created attachment 156828 [details] Actual results
(In reply to jakebrownstead from comment #0) > Created attachment 156826 [details] > Spreadsheet of bug > > Upon using the AGGREGATE function and selecting any of the options to ignore > errors, Calc does not ignore DIV/0 errors. What options did you select to ignore errors? And please add info from Help->About dialog
The second parameter Options with argument 3 means to "Ignore hidden rows, errors, nested SUBTOTAL and AGGREGATE functions", see https://help.libreoffice.org/6.3/en-US/text/scalc/01/func_aggregate.html
Problem here seems to be that already the calculation of its argument yields an error and thus ends the calculation and AGGREGATE() is not even called.
Another problem is that for the expected results the subexpressions that yield the 3rd and 4th arguments to AGGREGATE() need to be evaluated in array mode, otherwise already ($A$3:$A$10=$D$2)/($A$3:$A$10=$D$2) with its implicit intersection of the formula cell's position and $A$3:$A$10 results in #DIV/0! and multiplied with the array of ROWS() just creates an array of #DIV/0!, of which ignoring the errors of course does not produce a result in SMALL(). I wonder if for the expected case of {1,2,3,5,8,,,} it shouldn't be {1,2,3,#VALUE!,5,#VALUE!,#VALUE!,8} instead, matching the actual row positions. Otherwise we may have to force array mode on AGGREGATE() arguments for non-references. How does this work in Excel? Can someone please try?
Created attachment 156976 [details] A screenshot of Excel2016 (In reply to Eike Rathke from comment #6) Here it is :-)
I suppose that for {1,2,3,DIV/0,5,DIV/0,DIV/0,8} case, sorting the array (which is what SMALL does, right?) should consider any DOV/0 (=infinity) as larger than any normal number.
Or for the AGGREGATE's "ignore errors" 2nd parameter (2, 3, 6, 7), it should just pretend the values do not exist in the array - equal to array being simply smaller by count of errors... - which would give #VALUE for SMALL's RankC argument greater than (array size - error count), which seems to be the case for Excel.
Version: 6.3.4.2.0+ Build ID: 6.3.4-1 CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: kde5; Locale: en-AU (en_AU.UTF-8); UI-Language: en-US Calc: threaded Tried AGGREGATE(15) function SMALL, and options 2,3 & 6; all contain options to ignore errors.
(In reply to Eike Rathke from comment #5) > Problem here seems to be that already the calculation of its argument yields > an error and thus ends the calculation and AGGREGATE() is not even called. Upon further investigation that seems more likely. As when tested without dynamic row allocations it does infact ignore errors.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/78d4e78c43f688a3617d659774d985d466bb39dc Resolves: tdf#129681 Handle array/matrix in AGGREGATE() with ignore errors It will be available in 6.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.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/389008b0497793283e652050e32267370326855f tdf#129681: sc: Add unittest It will be available in 7.1.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.
Just tested with V 7.1.1.1 due to https://ask.libreoffice.org/en/question/297607/ Worked correctly with the examples attached there.