Issue 32345 - ODFF: Make LARGE() and SMALL() return an array if the second parameter is an array.
Summary: ODFF: Make LARGE() and SMALL() return an array if the second parameter is an ...
Status: REOPENED
Alias: None
Product: Calc
Classification: Application
Component: programming (show other issues)
Version: OOo 1.0.0
Hardware: All All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords: ms_interoperability
Depends on:
Blocks:
 
Reported: 2004-07-30 12:05 UTC by ooo
Modified: 2017-05-20 11:11 UTC (History)
3 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Attachments
MS-Excel file(Created Excel 2003). (13.50 KB, application/vnd.ms-excel)
2008-05-20 11:51 UTC, masayan 		no flags Details
capture of original(Excel file) result. (26.16 KB, image/png)
2008-05-20 11:53 UTC, masayan 		no flags Details
capture of opening by OOo2.4.0 original(Excel file) result. (52.89 KB, image/png)
2008-05-20 11:54 UTC, masayan 		no flags Details
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this issue.
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 
Accepted.
Comment 2 ooo 2004-07-30 12:30:37 UTC 
reassign to myself
Comment 3 ooo 2004-07-30 12:30:55 UTC 
Accepted.
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.

  Eike
Comment 5 ooo 2006-01-24 11:17:22 UTC 
Closing.
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 ( http://oooug.jp/faq/index.php?faq%2F4%2F1024 )
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
=SUM(LARGE(A1:A10;{1;2;3})).

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 "issues@openoffice.apache.org".