Suppose an Excel file has vectorized 2-by-2 symmetric matrices line by line as follows:

Thus, the first value of DETERMINANT column can be computed from a matrix {2 0,0 3}, the second value can be from a matrix {5 0,0 7}, and so on. Instead of reshaping the A1:C1 values, the D1 value can be directly computed via =MDETERM(IF(A1:B1=TRANSPOSE(A1:B1),A1:B1,C1)) because IF can actually reshape the A1:C1 values to the diagonal matrix of interest.

The point is that, in Excel, matrix functions such as MDETERM directly require matrices, while the matrices in the data are stored row by row after vectorization. Unfortunately, the IF solution above can only reshape 2-by-2 symmetric matrices and handle neither asymmetric matrices nor matrices with more than two off-diagonal elements. Do we have any other solutions more general than the illustrated IF approach to unvectorize matrices?

In addition, I am also interested in vectorizing matrices in Excel as well; for example, the matrix in A1:C1 can be reshaped, inverted, and then restored in the D1:G1 area. Thanks for your help!

CodePudding user response：

I think you might have meant

=IF(COLUMN(A1:B1)=TRANSPOSE(COLUMN(A1:B1)),A1:B1,C1)

But you can certainly expand on this idea. Suppose you have the diagonal elements of a symmetric 4X4 matrix in A1:D1 and the off-diagonal elements in E1:J1.

Then you could use

=IF(ROW(A1:D4)=COLUMN(A1:D4),INDEX(A1:J1,ROW(A1:D4)),
IF(ROW(A1:D4)<COLUMN(A1:D4),INDEX(E1:J1,(ROW(A1:D4)-1)*4 COLUMN(A1:D4)-ROW(A1:D4)*(ROW(A1:D4) 1)/2),
INDEX(E1:J1,(COLUMN(A1:D4)-1)*4 ROW(A1:D4)-COLUMN(A1:D4)*(COLUMN(A1:D4) 1)/2)))

A bit more general with Excel 365 Let function:

=LET(N,4,mat,A1:INDEX(A1:Z26,N,N),
IF(ROW(mat)=COLUMN(mat),
INDEX(1:1,ROW(mat)),
IF(ROW(mat)<COLUMN(mat),
INDEX(1:1,(ROW(mat)-1)*N COLUMN(mat)-ROW(mat)*(ROW(mat) 1)/2 N),
INDEX(1:1,(COLUMN(mat)-1)*N ROW(mat)-COLUMN(mat)*(COLUMN(mat) 1)/2 N))))