G Guest Jun 25, 2006 #1 excel 2003 or 2007 beta 2 How can I get the diagonal of a matrix returned as a single column vector?
G Guest Jun 26, 2006 #2 JoeNiner said: How can I get the diagonal of a matrix returned as a single column vector? Click to expand... 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"))) ---
JoeNiner said: How can I get the diagonal of a matrix returned as a single column vector? Click to expand... 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 Jun 26, 2006 #3 =INDEX(B2:E2,,ROWS($1:1)) 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"))) --- Click to expand...
=INDEX(B2:E2,,ROWS($1:1)) 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"))) --- Click to expand...
H Harlan Grove Jun 26, 2006 #4 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) Click to expand... .... 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)
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) Click to expand... .... 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 Jun 26, 2006 #5 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)) Click to expand... 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) Click to expand... 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 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)) Click to expand... 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) Click to expand... 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 Jun 26, 2006 #6 Max wrote... Thanks for correction, Harlan. Click to expand... 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 ? Click to expand... 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.
Max wrote... Thanks for correction, Harlan. Click to expand... 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 ? Click to expand... 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.
