In a recent post, I showed how you can multiply matrices in Excel. That post led to a great question from an EngineerExcel subscriber: Is there a way to do complex matrix multiplication in Excel? The answer is yes, there is!
Complex Numbers in Excel
Complex numbers are stored in Excel cells as text strings, and we must use special functions to extract the real and imaginary parts of the numbers so that we can perform mathematical operations on them.
Because complex numbers are stored as text strings in the cells, if we try to use MMULT to multiply two complex matrices, we’ll get a #VALUE! error:
So, instead of trying to multiply the complex matrices directly we need to represent the complex matrices as the sum of two matrices (A + Bi). Where A contains the real numbers and B contains the imaginary numbers. We can do that using the IMREAL and IMAGINARY functions:
Elevate Your Engineering With Excel
Advance in Excel with engineering-focused training that equips you with the skills to streamline projects and accelerate your career.
Complex Matrix Multiplication in Excel
Once we are done, we have four matrices: A, B, D, and F. And the product of the two complex matrices can be represented by the following equation:
Doing the arithmetic, we end up with this:
Since i^2 is equal to -1, the expression can be rewritten:
Finally, we can regroup the real and imaginary numbers:
Now, we can use the conventional MMULT function to perform the matrix multiplication.
First, we’ll calculate (AD – BF), or the resulting matrix of real numbers.
Next, we can calculate (AF + BD), the matrix of imaginary numbers.
Calculating the Result
Finally, we can use the COMPLEX function to assemble the real and imaginary matrices into a single complex matrix:
Alternatively, we could wrap all three of those operations in one big array formula:
So that’s how you can perform complex matrix multiplication in Excel using a little bit of arithmetic and Excel’s built-in functions. If this is a calculation you need to perform frequently, it would be a great candidate for creating a User Defined Function in VBA. Otherwise, you can just follow the steps I’ve laid out above to perform the analysis without any VBA knowledge required.
