Bug 105120 - RANK.EQ function compatibility with excel2016 online
Summary: RANK.EQ function compatibility with excel2016 online
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Hardware: All All
: medium normal
Assignee: Not Assigned
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:

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

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
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

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

Actual Results:  

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.