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

[Guest]

excel 2003 or 2007 beta 2

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

 

[Guest]

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")))


---

 

[Biff]

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

Copied down

Biff

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")))


---

 

[Harlan Grove]

Max wrote...

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)

 

[Guest]

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 ?

---

 

[Harlan Grove]

Max wrote...

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.

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.

 

[Guest]

Thanks for the clarifications, Harlan.
Much appreciated.

---