What does MMULT Do in Excel?
MMULT is a Math & Trigonometry function in excel, stands for “Matric Multiplication” and returns the product of two arrays of two matrices where the number of rows of Matrix 1 is equal to the number of columns of Matrix 2.
This function applies the logic of multiplying one matrix by another matrix by using the “Dot Product” of rows and columns. For example, look at the two matrix tables in excel.
In the above image, we have two matrix tables, Matrix 1 & Matrix 2. Multiplication of matrix done by using the “Dot Product” method of rows and columns, i.e., a first row of “Matrix 1” is multiplied with the first column of “Matrix 2”.
The first column of “Matrix 1” has three numbers, and the first row of “Matrix 2” has three numbers. The first number of Matrix 1 columns will be multiplied with the first number of Matrix 2 row, and like this, all the values are multiplied, and in the end, a summation of these values has arrived as a result.
This is exactly what will be done by the MMULT function in excel as well.
This function has two arguments, i.e., Array 1 & Array 2.
- Array 1 will be “Matrix 1” table values and
- Array 2 will be “Matrix 2” values.
One special note we all need to be aware of is MMULT is an array function, so after selecting the matrix 1 & matrix 2 values, we need to close the formula as an array formula by pressing CTRL + SHIFT + ENTER keys.
Array Formulas in excel Closing Keys.
I know it is quite difficult to understand the formula logic by just reading the theoretical part, but nothing to worry about because we will show you enough examples with proper explanations to understand better.
How to Use the MMULT Function in Excel? (with Examples)
For example, consider below two matrices for this example.
By multiplying these two Matrix, we will create “Matrix 3”. To apply the MMULT function first, we need to decide the matrix table length. This is decided by the number of rows of Matrix 1 and the number of columns if Matrix 2.
- In this example, for Matrix 1, we have two rows, and for Matrix 2, we have two columns, so Matrix 3 will be 2 * 2 matrix. So select 2 * 2 rows and columns to create new Matrix 3.
- Step 1: Open the MMULT function.
- Step 2: For Array 1 argument, select Matrix 1 table values from A3 to C4.
- Step 3: For Array 2 argument, select Matrix 2 table values from E3 to F5.
- Step 4: Close the bracket but do not hit the enter key straight away because since this is an array formula, we need to hit the enter key by holding the Ctrl key and Shift key together. When we close the formula by holding Ctrl + Shift + Enter key, we get curly brackets as follows.
So we have got the result in Matrix 3.
If you are wondering how we got these values, then we will explain it to you in detail now.
The first row values of Matrix 1 are “2, 1, 7,” and the first column values of Matrix 2 are “8, 8, 2,” and the first value of Matrix 3 is “38”. This is achieved following the below equation.
- =(2 * 8) + (1 * 8) + (7 * 2)
- =16 + 8 + 14
- = 38
Like this second value in Matrix 3 is 27, this is achieved by first row values of Matrix 1 is “2, 1, 7” and second column values of Matrix 2 is “3, 7, 2”.
- =(2 * 3) + (1 * 7) + (7 * 2)
- =6 + 7 + 14
- = 27
Like this, MMULT performs its job.
Now, look at below two equations.
This time we have 3 * 3 matrix, so the new matrix will be the number of rows of Matrix 1 and the number of columns of Matrix 2. So new Matrix 3 will also be 3 * 3 matrix only.
- Now apply the MMULT function.
- Press Ctrl + Shift + Enter key to get the result.
Things to Remember
- MMULT is an array function so close with array function keys Ctrl + Shift + EnterCtrl + Shift + EnterCtrl-Shift Enter In Excel is a shortcut command that facilitates implementing the array formula in the excel function to execute an intricate computation of the given data. Altogether it transforms a particular data into an array format in excel with multiple data values for this purpose..
- To multiply matrix, the number of columns of Matrix 1 should be equal to the number of rows of Matrix 2; otherwise, we will get #VALUE! Error#VALUE! Error#VALUE! Error in Excel represents that the reference cell the user has either entered an incorrect formula or used a wrong data type (mostly numerical data). Sometimes, it is difficult to identify the kind of mistake behind this error..
This has been a guide to MMULT Function in Excel. Here we discuss how to use the MMULT function to get the multiplication of a matrix with examples and a downloadable excel template. You can learn more from the following articles –