How do I get the diagonal of a matrix from excel?

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" <[email protected]> wrote in message
news:[email protected]...
> "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
---

=SUMPRODUCT(N(OFFSET(B2,ROW(INDIRECT("1:4"))-1,ROW(INDIRECT("1:4"))-1))) works really well. But I'm wondering if there's a way to make it sum up-and-to-the-right rather than down-and-to-the-right?