[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

*Subject*: **INDEX needs different syntax/semantics**

*From*:**Eike Rathke <erack@sun.com>***To*: OASIS ODFF SC <office-formula@lists.oasis-open.org>*Date*: Wed, 11 Jul 2007 21:14:50 +0200

Hi, For interoperability with Excel we need to redefine the INDEX function's syntax and semantics. We currently have: Syntax: INDEX( Reference DataSource ; Integer Row ; Integer Column [ ; Integer AreaNumber = 1 ] ) ECMA/MOOXML defines: array form: INDEX ( array , [ row-number ] [ , [ column-number ] ] ) reference form: INDEX ( reference [ , [ row-number ] [ , [ column-number ] [ , [ area-number ] ] ] ] ) The somewhat strange sounding semantics (see ECMA document) for the array form actually do make some sense from the user's perspective for ease of use: If 'array' is a one-dimensional column vector, the column-number is optional or can be omitted as an empty parameter and the row-number is taken as the offset (1-based) into the vector. For a row vector the row-number is optional or can be omitted as an empty parameter. For example: =INDEX( A1:A3; 2 ) => value of A2, identical to INDEX( A1:A3; 1; 2 ) =INDEX( A1:A3; ; 2 ) with an omitted row-number is accepted as well =INDEX( A1:A3; ) returns value of A1 =INDEX( A1:A3; ; ) returns value of A1 =INDEX( A1:C1; 2 ) => value of B1, identical to INDEX( A1:C1; 2; 1 ) =INDEX( A1:C1; 2; ) with an omitted column-number is accepted as well =INDEX( A1:C1; ; 1 ) returns value of A1 =INDEX( A1:C1; ) returns an error =INDEX( A1:C1; ; ) returns an error The syntax given for the reference form actually is not quite correct, at least in the UI Excel doesn't accept =INDEX(A1:A3) with no further parameters. Furthermore, if array or reference is not a column vector or row vector but a two-dimensional range, an omitted row-number or column-number parameter makes INDEX return an entire column vector respectively row vector of the array/reference given, the same if either row-number or column-number is zero. Else the value of the intersection is returned. I propose the following syntax: INDEX( Reference|Array DataSource ; [ Integer Row ] [ ; [ Integer Column ] ] [ ; Integer AreaNumber = 1 ] ) and to give explanation in the semantics. Opinions? Eike -- Automatic string conversions considered dangerous. They are the GOTO statements of spreadsheets. --Robert Weir on the OpenDocument formula subcommittee's list.

**Follow-Ups**:**Re: [office-formula] INDEX needs different syntax/semantics***From:*"David A. Wheeler" <dwheeler@dwheeler.com>

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]