Hi,
I work often with named arrays (like range("matrix")).

I need
1. to address sub-matrices - for example entire i-th column of
"matrix". But in formulas (like {=matrix(1.column) -
matrix(2.column)}, or matrix(2..3,2..5) - it denotes submatrix of
"matrix" consisting of intersection of rows 2 and 3 and columns 2 to
5.

2. to have some functions like rowSums, colSums for instant summing of
matrix rows and cols (again for named array as parameter).

Briefly I want to use array-formulas with names of arrays as operands
instead of referencing cells.

Do I need to program it myself or is it anywhere in Excel or is it
already programmed by anybody?

thanks Martin

Martin Mrazek wrote:
Hi,
I work often with named arrays (like range("matrix")).

I take it you mean named ranges, not named arrays. Assuming proper
qualification,

=INDEX(matrix,n,0) will return an array of the values in the nth row of
the range named "matrix";

=INDEX(matrix,0,n) will return an array of the values in the nth column.

=SUM(INDEX(matrix,n,0)) will therefore return the sum of the numeric
values in the nth row, and =SUM(INDEX(matrix,0,n)) will return the sum
of the numeric values in the nth column.

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, the
following will return the subarray of the intersection of the 2nd to 5th
columns and the 2nd to 3rd rows of the range named "matrix":

=SubArray(matrix, 2, 5, 2, 3)

And if you chose to add that file to your library, then if you were
working in VBA rather than on the worksheet, RowVector(myMatrix, n) will
return an array of the elements in the nth row of the array referred to
by the variable myMatrix, and ColumnVector(myMatrix, n) will return an
array of the elements in the nth column. These are not as efficient in
execution as the VBA analogs of the INDEX functions above, but in some
circumstances can present functionality advantages.

On the worksheet, =RowVector(matrix, n) and =ColumnVector(matrix, n) are
also available, but are not as efficient in execution as the INDEX
functions above.

Alan Beban


I need
1. to address sub-matrices - for example entire i-th column of
"matrix". But in formulas (like {=matrix(1.column) -
matrix(2.column)}, or matrix(2..3,2..5) - it denotes submatrix of
"matrix" consisting of intersection of rows 2 and 3 and columns 2 to
5.

2. to have some functions like rowSums, colSums for instant summing of
matrix rows and cols (again for named array as parameter).

Briefly I want to use array-formulas with names of arrays as operands
instead of referencing cells.

Do I need to program it myself or is it anywhere in Excel or is it
already programmed by anybody?

thanks Martin