excel 2003 or 2007 beta 2

How can I get the diagonal of a matrix returned as a single column vector?

"JoeNiner" wrote:
How can I get the diagonal of a matrix returned as a single column vector?

One try ..

Assuming a 4 x 4 grid in B2:E5 (top left cell is B2)

Perhaps this expression:
=OFFSET(B2,ROW(INDIRECT("1:4"))-1,ROW(INDIRECT("1:4"))-1)

Or maybe this expression below,
array-entered into a 4 cell columnar range, say into B20:B23 :
=INDEX(B2:E5,ROW(INDIRECT("1:4")),ROW(INDIRECT("1: 4")))

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

=INDEX(B2:E2,,ROWS($1:1))

Copied down

Biff

"Max" wrote in message
...
"JoeNiner" wrote:
How can I get the diagonal of a matrix returned as a single column
vector?

One try ..

Assuming a 4 x 4 grid in B2:E5 (top left cell is B2)

Perhaps this expression:
=OFFSET(B2,ROW(INDIRECT("1:4"))-1,ROW(INDIRECT("1:4"))-1)

Or maybe this expression below,
array-entered into a 4 cell columnar range, say into B20:B23 :
=INDEX(B2:E5,ROW(INDIRECT("1:4")),ROW(INDIRECT("1: 4")))

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Max wrote...
"JoeNiner" wrote:
How can I get the diagonal of a matrix returned as a single column vector?

One try ..

Assuming a 4 x 4 grid in B2:E5 (top left cell is B2)

Perhaps this expression:
=OFFSET(B2,ROW(INDIRECT("1:4"))-1,ROW(INDIRECT("1:4"))-1)
....

FWIW, can't use the expression above as a term in an array formula.
It's need to be

N(OFFSET(B2,ROW(INDIRECT("1:4"))-1,ROW(INDIRECT("1:4"))-1))

This can be done without volatile functions. As a column vector,

=MMULT((ROW(M)-MIN(ROW(M))=COLUMN(M)-MIN(COLUMN(M)))*M,
TRANSPOSE(COLUMN(M))^0)

"Harlan Grove" wrote:
FWIW, can't use the expression above as a term in an array formula.
It's need to be
N(OFFSET(B2,ROW(INDIRECT("1:4"))-1,ROW(INDIRECT("1:4"))-1))

Thanks for correction, Harlan.

This can be done without volatile functions. As a column vector,
=MMULT((ROW(M)-MIN(ROW(M))=COLUMN(M)-MIN(COLUMN(M)))*M,
TRANSPOSE(COLUMN(M))^0)

I suppose M is a defined range referring to the matrix,
eg to the 4 x 4 grid in B2:E5

Does the N(..) need to be likewise applied here ?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Max wrote...
"Harlan Grove" wrote:
FWIW, can't use the expression above as a term in an array formula.
It's need to be
N(OFFSET(B2,ROW(INDIRECT("1:4"))-1,ROW(INDIRECT("1:4"))-1))

Thanks for correction, Harlan.

Wasn't a correction per se. Your formula works when entered as an array
formula into 1-column by 4-row ranges, but

=SUMPRODUCT(OFFSET(B2,ROW(INDIRECT("1:4"))-1,ROW(INDIRECT("1:4"))-1))

returns #VALUE! errors while

=SUMPRODUCT(N(OFFSET(B2,ROW(INDIRECT("1:4"))-1,ROW(INDIRECT("1:4"))-1)))

returns expected results.

This can be done without volatile functions. As a column vector,
=MMULT((ROW(M)-MIN(ROW(M))=COLUMN(M)-MIN(COLUMN(M)))*M,
TRANSPOSE(COLUMN(M))^0)

I suppose M is a defined range referring to the matrix,
eg to the 4 x 4 grid in B2:E5

Correct.

Does the N(..) need to be likewise applied here ?

Why don't you test it? Hint, try the array formula

=SUM(MMULT((ROW(M)-MIN(ROW(M))=COLUMN(M)-MIN(COLUMN(M)))*M,
TRANSPOSE(COLUMN(M))^0))

It has to be an array formula because both MMULT and TRANSPOSE require
array formula entry.

Thanks for the clarifications, Harlan.
Much appreciated.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---