Bug 129681 - AGGREGATE doesn't ignore DIV/0 errors when using the options supplied, while using dynamically generated rows
Summary: AGGREGATE doesn't ignore DIV/0 errors when using the options supplied, while ...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.3.4.2 release
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:6.5.0 target:7.1.0
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2019-12-29 06:13 UTC by jakebrownstead
Modified: 2020-06-26 16:36 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Spreadsheet of bug (13.67 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-12-29 06:13 UTC, jakebrownstead
Details
Expected results (445.40 KB, image/jpeg)
2019-12-29 06:15 UTC, jakebrownstead
Details
Actual results (459.22 KB, image/jpeg)
2019-12-29 06:15 UTC, jakebrownstead
Details
A screenshot of Excel2016 (71.75 KB, image/png)
2020-01-07 07:05 UTC, Mike Kaganski
Details

Note You need to log in before you can comment on or make changes to this bug.
Description jakebrownstead 2019-12-29 06:13:52 UTC
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.
Comment 1 jakebrownstead 2019-12-29 06:15:05 UTC
Created attachment 156827 [details]
Expected results
Comment 2 jakebrownstead 2019-12-29 06:15:45 UTC
Created attachment 156828 [details]
Actual results
Comment 3 Roman Kuznetsov 2020-01-04 14:29:27 UTC
(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
Comment 4 Eike Rathke 2020-01-06 16:55:11 UTC
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
Comment 5 Eike Rathke 2020-01-06 17:08:50 UTC
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.
Comment 6 Eike Rathke 2020-01-06 22:51:53 UTC
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?
Comment 7 Mike Kaganski 2020-01-07 07:05:33 UTC
Created attachment 156976 [details]
A screenshot of Excel2016

(In reply to Eike Rathke from comment #6)

Here it is :-)
Comment 8 Mike Kaganski 2020-01-07 07:08:09 UTC
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.
Comment 9 Mike Kaganski 2020-01-07 07:20:05 UTC
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.
Comment 10 jakebrownstead 2020-01-07 11:00:16 UTC
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.
Comment 11 jakebrownstead 2020-01-07 11:28:38 UTC
(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.
Comment 12 Commit Notification 2020-01-08 10:33:26 UTC
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.
Comment 13 Commit Notification 2020-06-26 16:36:42 UTC
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.