Excel MMULT ( Table of Contents)
Introduction to MMULT in Excel
In mathematical terms, the phenomena of multiplying two matrices with each other are called matrix multiplication. Multiplying two matrices has certain rules, though. Since the product of matrices is given by multiplying elements of each row of the first matrix with the elements of each column of the second matrix, it becomes mandatory to have a number of columns from the first matrix equal to the number of rows from the second matrix.
What is MMULT Function in Excel?
In Excel, we have a function called “MMUL”, an array function, which is a part of a variety of formulae under the “Math & Trigonometry” function, which can be found under the Formulas section. MMULT can be used to have a product of two matrix arrays within Excel in which, number of rows from the first matrix equals to the number of columns from the second matrix.
Below is the syntax for the MMULT function in Excel.
- array1 – Is a required argument for the function that specifies the first matrix array which you want to multiply.
- array2 – Is a required argument for the function that specifies the second matrix array which you want to multiply.
A comma separates both these arguments in the MMULT function. It means, after you entered the first array in the formula, you should mention comma (“,”) before you add the second array argument.
As it has already been mentioned at the start, MMULT is an array function. Thus, while executing it, you need to press Ctrl + Shift + Enter button simultaneously instead of directly press the Enter key through your keyboard.
Examples of MMULT in Excel
Let’s have hands-on experience with some examples.
Example #1 – Using MMULT to Multiply Two Matrices
Suppose we have two matrices with two rows, three columns, and three rows two columns respectively, as shown below.
After we go for a product of these two matrices, they at most can produce two rows and two columns (this is because we have two rows in First Matrix and two columns in Second Matrix). Hence, our resultant matrix will be a 2X2 matrix. We will store the same under cells varying from A6:B8 as shown below:
Step 1: Select all the cells (A7:B8) from Resultant Matrix to apply the formula at once.
Step 2: Inside the active cell (cell A7), start initiating the formula for matrix multiplication. Use =MMULT( in the cell to initiate the formula.
Step 3: Use First Matrix cells, i.e. from A2 to C3, as a first array argument under the formula for Matrix Multiplication. Give comma (“,”) after the first array is set in the formula.
Step 4: Use Second Matrix cells, i.e. from E2 to F4, as a second array argument under the formula for Matrix Multiplication. Close the parentheses to complete this formula.
Step 5: The formula is now complete. But don’t press the Enter button directly. Remember, the MMULT function is an array function. Instead of pressing Enter, use Ctrl + Shift + Enter to get the output of this formula.
You’ll get the output of multiplication/product of two matrices as shown below across cells A7:B8:
Notice the curly open and close braces under which the formula for MMULT is wrapped. These braces are indicators that the formula we have entered is an array formula or is applied on arrays.
How does the formula work? Let’s see below:
The matrix multiplication is like each element of every row from the first matrix gets multiplied by each element of every column from another matrix. Then, we will sum all the element-wise values to get a single value. The first row for First Matrix is 2, 6, 3, and the first column of the Second Matrix has values 2, 7, 4. Going with element-wise multiplication, we will get –
(2*2) = 4, (6*7) = 42 and (3*4) = 12 as multiplication results. Now, we will add these three values together to get the first element of the Resultant Matrix.
4 + 42 + 12 = 58.
Thus, the first element of the Resultant Matrix will be 58.
Example #2 – Using MMULT to Multiply Two Matrices
Suppose we have two matrices as shown below with three rows and three columns:
We will use the MMULT to have the product of these two matrices and get a new matrix out of it called as Resultant Matrix, which can be stored in A7:C9.
Since we have three rows in the First Column and the same number of columns in the Second Matrix, the Resultant Matrix will be a matrix with three rows and three columns.
Step 1: Select all cells across A7:C9 under Resultant Matrix and initiate a formula for MMULT in cell A7.
Step 2: Use First Matrix as a first argument under MMULT function, which is spread across A2:C4. Use a comma to separate the first argument from the second.
Step 3: Use the Second Matrix as a second array argument under the MMULT function, which is spread across E2:G4. Complete the formula by adding a parenthesis that closes the formula.
Step 4: Now, press Ctrl + Shift + Enter to get the output of this array formula instead of the press Enter key. You’ll get an output as shown below:
This is how we can use the MMULT function to get the matrix multiplication for two matrix arrays. Let’s wrap the things up with some points to be remembered:
Things to Remember
- Since the MMULT function returns an array value, it must be entered as an array formula. For this to happen, you must use Ctrl + Shift + Enter button instead of entering the button at the time you execute the MMULT formula.
- You will get a #VALUE! error if the number of rows from the First Matrix and the column from the Second Matrix does not match each other. Also, if any of the cells are empty or have text values stored in it for any matrix, we will get the same error.
- We may get the #N/A error if we have selected the cells within the Resulting Matrix that is not a part of it.
This is a guide to MMULT in Excel. Here we discuss How to use MMULT in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –