Issue 78781

Summary: ODFF: Excel INDEX() compatibility
Product: Calc Reporter: kuprishuz <kuprishuz>
Component: programmingAssignee: oc
Status: CLOSED FIXED QA Contact: issues@sc <issues>
Severity: Trivial    
Priority: P3 CC: issues, lvyue, pagalmes.lists
Version: OOo 2.2.1Keywords: ms_interoperability
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: PATCH Latest Confirmation in: ---
Developer Difficulty: ---
Issue Depends on:    
Issue Blocks: 72764    
Attachments:
Description Flags
patch1 none

Description kuprishuz 2007-06-21 21:05:05 UTC 
When using the INDEX(reference, row, column), Excel handles parameter
differently based on if a 3rd parameter was passed.

ex: 
   input: =INDEX($D$27:$P$27;2)
   ms result: (value of E27)
   oo result: err:504

i did some testing on this and it appears that when no 3rd param is passed that
excel uses the second as the column and assumes 1 for the row, as calc requires
that the a row is atleast a blank value when a column is used.
Comment 1 gercokees 2007-06-22 14:09:23 UTC 
I agree,
Excel handles the parameters a bit differently, specially with references with a
size of only one row.
Confirming this issue, keyword: MS-Interoperability
Comment 2 frank 2007-07-11 13:47:00 UTC 
Hi,

regarding your example, Excel works faulty. You have just one row and want a
result from the second row. Giving here other results than an Error is IMHO a fault.

Calc on the other hand gives you a hint that something in the parameterlist of
the function is faulty. This is how it should be. I think we should not copy the
errors Excel make.

Frank

@Eike
It seems that Excel treats such one row constructs in a special way. Should we
double this ? If so, please set the target accordingly otherwise close as invalid.
Comment 3 ooo 2007-07-11 15:48:50 UTC 
It's surely strange behavior, but it's not an error of Excel, that's the way how
they defined INDEX to work in what they call "array form" of the function with
no 4th parameter being present. With a one-dimensional array the row
respectively column parameter is optional, and whatever parameter is present is
taken for the offset.
Comment 4 ooo 2007-11-14 14:01:34 UTC 
ODFF relevant.
Comment 5 lvyue 2008-03-18 08:25:36 UTC 
Created attachment 52182 [details]
patch1
Comment 6 ooo 2008-04-11 17:06:13 UTC 
In cws odff03:

sc/source/core/tool/interpr1.cxx  1.56.20.4
Comment 7 ooo 2008-05-09 17:52:43 UTC 
Reassigning to QA for verification.
Comment 8 ooo 2008-05-28 14:05:05 UTC 
*** Issue 89550 has been marked as a duplicate of this issue. ***
Comment 9 oc 2008-06-09 12:36:16 UTC 
verified in internal build cws_odff3
Comment 10 gibi 2008-08-05 14:59:08 UTC 
Verified with DEV300-m28 Linux X64
Comment 11 sgautier.ooo 2008-08-05 15:02:27 UTC 
Verified in DEV300m28 and Linux - Closing - Sophie