G
Guest
excel 2003 or 2007 beta 2
How can I get the diagonal of a matrix returned as a single column vector?
How can I get the diagonal of a matrix returned as a single column vector?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Guest
JoeNiner said: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")))
---
B
Biff
=INDEX(B2:E2,,ROWS($1:1))
Copied down
Biff
Copied down
Biff
Max said: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")))
---
H
Harlan Grove
Max 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))
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)
....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)
G
Guest
Harlan Grove said: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 ?
---
H
Harlan Grove
Max wrote...
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.
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 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.
G
Guest
Thanks for the clarifications, Harlan.
Much appreciated.
---
Much appreciated.
---