Created attachment 151377 [details] see example in the attachment capture
Hello Djaafar Benchikh, Thank you for reporting the bug. I can confirm that the bug is present in master. Version: 6.3.0.0.alpha0+ Build ID: 98630a0bd49bd80652145a21e4e0d0ded792b36b CPU threads: 8; OS: Linux 5.0; UI render: default; VCL: kde5; TinderBox: Linux-rpm_deb-x86_64@86-TDF, Branch:master, Time: 2019-05-04_04:44:35 Locale: en-IE (en_IE.UTF-8); UI-Language: en-US Calc: threaded
Could you please say exactly what you think is wrong with the function? What I see in the screen shot you added, I see an expected result. It is possible to use numeric arguments, references and arrays. AFAIK this also applies for Excel. The help file states for this function: Ref1 – obligatory argument. The first numeric argument (if the range is set by a list of values inside the function) or a reference to a cell that contains it. Ref2, 3, ... – optional. A numeric argument or a reference to a cell (up to 253 arguments), for which you need the aggregate value.
the correct format of references is like A1, A2, B1, B2. while in CALC the aggregate function gives a result for an incorrect format of references aggregate(2,4,A1,A5) correcte format aggregate(2,4,1,1) incorrect format
(In reply to Djaafar Benchikh from comment #3) > the correct format of references is like A1, A2, B1, B2. while in CALC the > aggregate function gives a result for an incorrect format of references > > aggregate(2,4,A1,A5) correcte format > aggregate(2,4,1,1) incorrect format Ok, I wrongly interpreted the description of the function in Excel, which says that argument 3...n represents a numerical value and only the name of the argument (ref1...refx) suggests that the argument ought to be a reference and not a numerical value. I managed to use a computer with Excel and indeed, Excel does not accept numerical values as arguments. And as Calc's AGGREGATE is to be fully interoperable with Excel, it will be necessary to reduce the functionality of AGGREGATE in Calc. Always a pity if functionality is to be reduced. The same applies for SUBTOTAL, I changed the bug report's title to reflect this. I'll start 'fixing'.
Could someone enlighten me please and point to the definition that says that numbers are not allowed as arguments to AGGREGATE() (note these are not references and thus not an "incorrect format of references"). To me, AGGREGATE(4,6,1,1) producing 1 is correct as it calculates MAX(1,1).
(In reply to Eike Rathke from comment #5) > Could someone enlighten me please and point to the definition that says that > numbers are not allowed as arguments to AGGREGATE() (note these are not > references and thus not an "incorrect format of references"). To me, > AGGREGATE(4,6,1,1) producing 1 is correct as it calculates MAX(1,1). AGGREGATE() is not an ODFF1.2 function, but an Excel function. The text in https://support.office.com/en-us/article/aggregate-function-43b9278e-6aa7-4f17-92b6-e19993fa26df is IMHO somewhat ambiguous and my first interpretation was that AGGREGATE(4,6,1,1) would be valid for Excel. However, when trying this in Excel, it returns an error. Likewise with SUBTOTAL. I am not happy wit the behaviour of Excel, but as AGGREGATE is an Excel-function, I think we need to adhere to the Excel behaviour - bar bugs, of course. In the case of SUBTOTAL we have the complication that the function exists in Excel (where it does not allow numbers as arguments) and is defined in ODFF1.2 where numbers as arguments are allowed. To avoid confusion: 'do not allow numbers as arguments' _only_ applies to arguments a2...an (a2...an-1 in case of some function types that require an extra argument) : AGGREGATE(a0, a1, a2, ..., an) . AGGREGATE(4, 6, B1:B2) is valid for Excel.
While the "ref1, [ref2], …" parameters in that definition seem to indicate that only references are accepted, the similar definition for SUBTOTAL() at https://support.office.com/en-US/article/SUBTOTAL-function-7B027003-F060-4ADE-9040-E478765B9939 does the same, but the definition in OOXML ECMA-376-1:2016 18.17.7.305 SUBTOTAL says SUBTOTAL ( function-number , argument-list ) with argument-list of type number, no other restriction whatsoever. Unfortunately AGGREGATE() is not covered by OOXML. Anyway, if Excel has that behaviour it seems not well specified. (FWIW, Gnumeric 1.12.44 for SUBTOTAL() also accepts actual numbers, it doesn't know AGGREGATE()). However, my concern is that when changing the implementation of AGGREGATE() we'd introduce incompatibilities that could sacrifice existing documents without any need just for the questionable "benefit" of Excel compatibility restrictions. I don't see a compelling reason to do this.
(In reply to Eike Rathke from comment #7) > While the "ref1, [ref2], …" parameters in that definition seem to indicate > that only references are accepted, the similar definition for SUBTOTAL() at > https://support.office.com/en-US/article/SUBTOTAL-function-7B027003-F060- > 4ADE-9040-E478765B9939 does the same, but the definition in OOXML > ECMA-376-1:2016 18.17.7.305 SUBTOTAL says SUBTOTAL ( function-number , > argument-list ) with argument-list of type number, no other restriction > whatsoever. Unfortunately AGGREGATE() is not covered by OOXML. Anyway, if > Excel has that behaviour it seems not well specified. (FWIW, Gnumeric > 1.12.44 for SUBTOTAL() also accepts actual numbers, it doesn't know > AGGREGATE()). > > However, my concern is that when changing the implementation of AGGREGATE() > we'd introduce incompatibilities that could sacrifice existing documents > without any need just for the questionable "benefit" of Excel compatibility > restrictions. I don't see a compelling reason to do this. I share your concern. Also I don't like to reduce functionality. As the definition in OOXML ECMA-376-1:2016 18.17.7.305 SUBTOTAL is clear in allowing numbers and AGGREGATE is a similar function, I'm quite happy to use OOXML ECMA-376-1:2016 18.17.7.305 SUBTOTAL as general guideline and closing this bug report as NOTOURBUG. It might be useful to add AGGREGATE tot he ODFF standard, that way there will be at least one standard for the function.