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 at the end summation of these values are arrived as the result.
This is exactly what will be done by 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 because we will show you enough examples with proper explanations to understand better.
How to Use 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 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 enter key straight away because since this is an array formula we need to hit enter key by holding 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 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 new matrix will be the number of rows of Matrix 1 and 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 + Enter.
- 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.
This has been a guide to MMULT Function in Excel. Here we discuss how to use MMULT function to get the multiplication of a matrix with examples and downloadable template. You can learn more from the following articles –