Bug 105026 - PERMUTATIONA function - different results than excel online
Summary: PERMUTATIONA function - different results than excel online
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium normal
Assignee: Winfried Donkers
URL:
Whiteboard: target:5.4.0 target:5.3.0.2
Keywords:
Depends on:
Blocks:
 
Reported: 2017-01-01 11:34 UTC by raal
Modified: 2017-01-04 09:16 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
test case (276.23 KB, application/vnd.oasis.opendocument.spreadsheet-flat-xml)
2017-01-01 11:36 UTC, raal
Details

Note You need to log in before you can comment on or make changes to this bug.
Description raal 2017-01-01 11:34:41 UTC
Description:
 PERMUTATIONA function - different results then excel online

There is a constraint in the code (total >= selection) that I cannot find in ODFF1.2 nor in Excel information, nor in the definition of permutation with repetition. 

Steps to Reproduce:
1.open attached test case
2. save as xlsx
3.compare with excel

Actual Results:  
dofferent results than excel

Expected Results:
same results as in excel


Reproducible: Always

User Profile Reset: No

Additional Info:


User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:50.0) Gecko/20100101 Firefox/50.0
Comment 1 raal 2017-01-01 11:36:13 UTC
Created attachment 130071 [details]
test case
Comment 2 Eike Rathke 2017-01-02 20:35:43 UTC
To me it doesn't make sense to allow number of chosen objects (argument 2) to be greater than number of objects (argument 1), or what am I missing?
Comment 3 Eike Rathke 2017-01-02 20:44:11 UTC
Note that http://www.excelfunctions.net/excel-permutationa-function.html similarly says "#NUM! - Occurs if [...] The supplied number argument is < the number_chosen argument.", but https://support.office.com/en-US/article/PERMUTATIONA-function-6c7d7fdc-d657-44e6-aa19-2857b25cae4e doesn't mention it. Maybe different Excel versions treat it differently? Or one of the docs is wrong..
Comment 4 Eike Rathke 2017-01-02 20:54:57 UTC
Ah, my bad (or bad wording in the docs), it's not literally "number chosen" of a "number" of objects, but the number of possible tuples over a set of elements. So yes, there should be no restriction Total <= Chosen.
Comment 5 Winfried Donkers 2017-01-03 06:57:52 UTC
(In reply to Eike Rathke from comment #3)
> Note that http://www.excelfunctions.net/excel-permutationa-function.html
> similarly says "#NUM! - Occurs if [...] The supplied number argument is <
> the number_chosen argument.", but
> https://support.office.com/en-US/article/PERMUTATIONA-function-6c7d7fdc-d657-
> 44e6-aa19-2857b25cae4e doesn't mention it. Maybe different Excel versions
> treat it differently? Or one of the docs is wrong..

I stick to  https://support.office.com/en-US/article/.. when searching for specifications of Excel functions, as in the past I often came across differences between the two. And even then, in the past couple of months i have come across numerous occasions where the actual behaviour of Excel functions differ from the constraints/behaviour as stated in these 'articles'...
Comment 6 Commit Notification 2017-01-03 21:32:00 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=25625f18a282209235214674f2b81604ecd067ad

tdf#105026 Remove wrong constraint in PERMUTATIONA.

It will be available in 5.4.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 7 Commit Notification 2017-01-03 23:08:19 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "libreoffice-5-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=dd350f02992867aefcab88f332ff1c508b5924c9&h=libreoffice-5-3

tdf#105026 Remove wrong constraint in PERMUTATIONA.

It will be available in 5.3.0.2.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.