Forum Discussion
Formula for Complex Matrices
Hi Microsoft Community Hub,
I am excited to share a new method I have developed for solving matrices with complex numbers. As many of you know, handling complex matrix operations in environments like Excel or during manual computation often requires separating real and imaginary parts, which increases complexity and the margin for error.
I have conceptualized the formula which streamlines this process by providing a more direct approach. This is a dynamic formula and this formula works for any dimension of matrix (see the screenshot below).
=IFERROR(
BYROW(TRANSPOSE(WRAPROWS(LET(
mat, A2:J11,
coef, L2:L11,
re, MAP(mat, LAMBDA(a, IMREAL(a))),
im, MAP(mat, LAMBDA(a, IMAGINARY(a))),
MMULT(MINVERSE(HSTACK(VSTACK(re, im),
VSTACK(-im, re))), VSTACK(MAP(coef, LAMBDA(a, IMREAL(a))),
MAP(coef, LAMBDA(a, IMAGINARY(a)))))), ROWS(A2:J11))),
LAMBDA(a, COMPLEX(TAKE(a,, 1), TAKE(a,, -1)))),
"Singular Matrix")
I hope this solution helps someone.
Best regards,
IlirU
2 Replies
- m_tarlerSilver Contributor
I don't understand. In particular the use of the MINVERSE inside which is what I think is causing these whole number complex numbers to produce fractional values. This doesn't appear to be a 'simple' matrix multiplication. That said I made some tweaks to your formula to isolate the LAMBDA portion of the formula and also simplified some of the part inside:
=LET(matrix, A1:C3, coef, E1:E3, iMMULT, LAMBDA(mat,coef,IFERROR( BYROW( TRANSPOSE( WRAPROWS( LET( re, MAP(mat, IMREAL), im, MAP(mat, IMAGINARY), MMULT(MINVERSE(HSTACK(VSTACK(re, im), VSTACK(-im, re))), VSTACK(MAP(coef, IMREAL), MAP(coef, IMAGINARY))) ), ROWS(mat) ) ), LAMBDA(a, COMPLEX(TAKE(a,, 1), TAKE(a,, -1)))), "Singular Matrix")), iMMULT(matrix,coef))note how lines 2-14 are a single LAMBDA function you can pull out and use separately.
also note how you can simplify the MAP functions on lines 7,8,and 10
that all said I did a variation for the matrix multiplication I'm used to:
=LET(matrix, A1:C3, coef, E1:E3, iMMULT, LAMBDA(mat,coef,[complex_format], LET( imFormat, IF(ISOMITTED(complex_format),1,complex_format), rM, MAP(mat, IMREAL), iM, MAP(mat, IMAGINARY), rC, MAP(coef, IMREAL), iC, MAP(coef, IMAGINARY), out, MMULT(HSTACK(rM, iM), HSTACK(VSTACK(rC,-iC),VSTACK(iC,rC))), IF(imFormat,BYROW(out,LAMBDA(a,COMPLEX(TAKE(a,, 1), TAKE(a,, -1)))),out) )), iMMULT(matrix,coef))I also included an optional variable [complex_format] that if TRUE or empty will return the values as complex numbers or if FALSE then will leave them as 2 columns (i.e. if further calculations might be preferred)
here is the sheet showing a simple 3x3 with 3 coefficients followed by your formula in col H, my modification of your formula in I and then my version in J:
- IlirUIron Contributor
I left the MAP functions unsimplified so that someone who is not very familiar with Excel would not be confused when looking at these functions.
Anyway, thank you for your effort to 'simplify' my formula a little. I appreciate it.