Bug 105120 - RANK.EQ function compatibility with excel2016 online
Summary: RANK.EQ function compatibility with excel2016 online
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: filter:xlsx
Depends on:
Blocks: Calc-Function XLSX
  Show dependency treegraph
 
Reported: 2017-01-05 10:32 UTC by raal
Modified: 2017-11-08 15:25 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
test file (318.87 KB, application/xml)
2017-01-05 10:37 UTC, raal
Details
test file saved as xlsx (10.81 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-01-05 12:02 UTC, Winfried Donkers
Details
xlsx file after repair bij Excel2016 (14.49 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-01-05 12:03 UTC, Winfried Donkers
Details

Note You need to log in before you can comment on or make changes to this bug.
Description raal 2017-01-05 10:32:21 UTC
Description:
Upon comparison in Calc of the xlsx before (RANK.EQ.xlsx) and after
RANK.EQ-O2016.xlsx) repair, I notice that:
cell A2 contains formula =RANK.EQ(5, {3,5,2,6,1}, 1) before and value 4
after the repair;
cell A15 contains formula =RANK.EQ(32,(Q1:Q5~P1:P5~R1:R5)) before and
value 3 after the repair.

This looks like an OOXML-export issue with arrays or expressions as
argument. 

Steps to Reproduce:
1. open test file
2. save as xlsx
3.open in excel2016 online

Actual Results:  
error

Expected Results:
file open


Reproducible: Always

User Profile Reset: No

Additional Info:


User-Agent: Mozilla/5.0 (Windows NT 6.1; rv:50.0) Gecko/20100101 Firefox/50.0
Comment 1 raal 2017-01-05 10:37:03 UTC
Created attachment 130170 [details]
test file
Comment 2 Winfried Donkers 2017-01-05 12:02:31 UTC
Created attachment 130174 [details]
test file saved as xlsx
Comment 3 Winfried Donkers 2017-01-05 12:03:59 UTC
Created attachment 130175 [details]
xlsx file after repair bij Excel2016

Attachment 130170 [details] after repair by Excel 2016.
Comment 4 Winfried Donkers 2017-11-08 15:25:25 UTC
Cells A2 and A15 contains arguments (inline array, multiple reference) that cannot be handled by Excel Online. 
Cells A10 and A12 give seemingly different results in Excel because the times in the array M3:M9 are calculated and not exactly identical. Adding ROUNDSIG to the time calculation (e.g. =ROUNDSIG(L3-K3; 10)) solves that.