INDEX needs different syntax/semantics

[Eike Rathke <erack@sun.com>]

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.