Issue 32345

Summary: ODFF: Make LARGE() and SMALL() return an array if the second parameter is an array.
Product: Calc Reporter: ooo
Component: programmingAssignee: AOO issues mailing list <issues>
Status: REOPENED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: issues, kamataki, masaya.k
Version: OOo 1.0.0Keywords: ms_interoperability
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---
Description Flags
MS-Excel file(Created Excel 2003).
capture of original(Excel file) result.
capture of opening by OOo2.4.0 original(Excel file) result. none

Description ooo 2004-07-30 12:05:18 UTC
Follow-up task of Q-PCD issue 20494.
Allow a range as second parameter of LARGE() and SMALL()
Comment 1 ooo 2004-07-30 12:05:51 UTC
Comment 2 ooo 2004-07-30 12:30:37 UTC
reassign to myself
Comment 3 ooo 2004-07-30 12:30:55 UTC
Comment 4 ooo 2006-01-24 11:16:28 UTC
Closing issue. It seems this has already been implemented in OOo2.0 with the
general array handling capability. At least I wasn't able to produce a
range-as-second-parameter case in Excel that was not handled by OOo. If you
happen to know such case feel free to reopen this issue.

Comment 5 ooo 2006-01-24 11:17:22 UTC
Comment 6 masayan 2008-05-20 11:49:57 UTC
Please reopen this issue.

MS-Excel allows "=LARGE(A1:A10,{1,2,3})", OOo2.x "LARGE(A1:A10;{1;2;3})", and
looks like it works fine.

But, for example, use LARGE()/SMALL() with AVERAGE(), problem occured.

If "A1, A2, ..., A10 = 1, 2, ..., 10" and anothe cell(MS-Excel) = "
=AVERAGE(LARGE(A1:A10,{1,2,3}))", return value of AVERAGE() is 9.
(so that means (10+9+8)/3)

OOo imported avobe .xls file, AVERAGE() retunrs 10. LARGE() not works fine.

Plese look at 3 files which I attached.

#Japanese Q&A ( )
Comment 7 masayan 2008-05-20 11:51:37 UTC
作成された添付 (id=53791)
MS-Excel file(Created Excel 2003).
Comment 8 masayan 2008-05-20 11:53:33 UTC
作成された添付 (id=53792)
capture of original(Excel file) result.
Comment 9 masayan 2008-05-20 11:54:30 UTC
作成された添付 (id=53793)
capture of opening by OOo2.4.0 original(Excel file) result.
Comment 10 ooo 2008-05-28 15:43:12 UTC
Thanks for letting us know. Problem is that LARGE() and SMALL() do not return an
array in this case, but only a single value instead, as can also be seen with

Note that in Excel =SUM(LARGE(A1:A10,B1:B3)) with B1:B3 containing {1,2,3} is
not the same and does not evaluate as an array, but generates an error instead.
In array evaluation also a 2D array is handled as second parameter, as can be
seen with =COLUMNS(LARGE(A1:A10,{1,2;3,4})) and =ROWS(LARGE(A1:A10,{1,2;3,4}))

Reopening this issue and adapting summary to reflect detail.
Comment 11 ooo 2008-08-13 17:20:58 UTC
Other quirks observed in Excel:
For LARGE(list;N), ROUNDUP(N;0) is applied, not INT(N).
For SMALL(list;N), probably ROUNDDOWN(N;0) is applied, effectively being INT(N)
for positive numbers.
Comment 12 Marcus 2017-05-20 11:11:35 UTC
Reset assigne to the default "".