The MMULT function stands for “Matrix Multiplication”. It is a math and trigonometry function available in Microsoft Excel. The MMULT function multiplies two arrays and returns another matrix array. In this article, you will get to know the usage of the Excel MMULT function with 6 proper examples.
The above screenshot is an overview of the article, representing an application of the MMULT function in Excel. You’ll learn more about the methods along with the other functions to use the MMULT function precisely in the following sections of this article.
Download the Practice Workbook
You can download the Excel file from the link below and practice along with it.
Introduction to the MMULT Function
- Function Objective:
The MMULT function multiplies two arrays of numbers and returns another array of numbers.
- Arguments Explanation:
|array1||Required||The first array that you want to multiply.|
|array2||Required||The second array that you want to multiply.|
- Return Parameter:
A matrix of number arrays.
Basics of Matrix Multiplication
Suppose, we have two matrices, A and B. Where A is an m by n matrix and B is an n by p matrix.
The product of these two matrices, C = AB; can be written as
The product of A and B that is C can also be written as,
6 Examples to Use the MMULT Function in Excel
Example 1: Insert Number Arrays Manually in MMULT Function in Excel
The MMULT Function allows us to manually insert the number of arrays to get their product. To do this,
❶ First you have to select the number of cells as to the output array matrix dimension.
❷ Then in the top-left-corner cell of the selection area, you have to insert the formula with the MMULT function. For this instance, the formula is:
❸ After that, press the CTRL + SHIFT + ENTER button to execute the formula.
After pressing the CTRL + SHIFT + ENTER, you will see the carl brackets wrapped up in the formula. This is because the formula is in the form of a legacy array formula.
If you are using Microsoft Office 365, then you don’t need to select the range of cells and then press CTRL + SHIFT + ENTER. Because Office 365 supports dynamic array formulas. That’s why all you need to do is just insert the formula and then press the ENTER button only.
Example 2: Multiply Two 3×3 Matrices Using the MMULT Function in Excel
In this section, we will calculate the multiplication of two square matrices having a dimension of 3×3.
The first array has a dimension of 3×3 and the second array has also a dimension of 3×3. As a result, the final matrix will have a dimension of 3×3 too.
Now follow the steps below to multiply two matrices using the MMULT function.
❶ Firstly, select a range of cells having a measurement of 3×3, as the output matrix dimension will be 3×3.
❷ Then insert the following formula onto the top-left corner of the selection area. Cell B10 for this instance.
Here B5:D7 is the range of the first array and F5:H7 is the range of the second array.
❸ Finally press the CTRL + SHIFT + ENTER buttons altogether to execute the formula.
As the formula is a legacy array formula, the selection area will be filled with the output numbers. You don’t need to drag the formula to all the corresponding cells.
As Microsoft Office 365 supports dynamic array formulas, you can just insert the formula with the MMULT function and then press the ENTER button. The dynamic array formula will automatically cover all the cells as to the dimension of the output matrix.
Example 3: Calculate the Product of a 2×3 Matrix with a 3×2 Matrix Using the MMULT Function in Excel
This time, instead of taking two identical matrices, you are considering two arrays of different dimensions.
The first array is a 2×3 number matrix and the second one is a 3×2 matrix. So the number of rows in the first matrix is 2 and the number of columns in the second matrix is 2. As a result, the dimension of the final matrix will be 2×2.
Now to multiply them using the MMULT function, follow the steps below.
❶ Select 4 consecutive cells, having 2 rows and two columns.
❷ Insert the following legacy array formula to the top-left corner of the selected cells.
❸ Hit the CTRL + SHIFT + ENTER buttons to execute the formula.
This procedure is applicable to all versions of Microsoft Excel, except Office 365.
To do the same task in Excel Office 365, just insert the formula in any cell and then press the ENTER button.
Example 4: Get the Multiplication of a 3×2 Matrix with a 2×3 Matrix Using the MMULT Function in Excel
This time the first array has a dimension of 3×2 and the second one has a dimension of 2×3. So the output array will have a dimension of 3×3.
Now follow the steps below to get the product of the two arrays using the MMULT function.
❶ First of all, select an area of 3×3 as the dimension of the output array will be 3×3.
❷ Input the following formula in the first cell of the selection area. Cell B10 for this instance.
❸ Hit the CTRL + SHIFT + ENTER buttons altogether.
For the Microsoft Office 365 user, just insert the dynamic array formula in cell B10 and hit the ENTER button. The dynamic formula will automatically fit the required dimension of the output array.
Example 5: Multiply a 3×1 Matrix with a 1×3 Matrix Using the MMULT Function in Excel
Now we are taking a 3×1 matrix and a 1×3 matrix. The number of rows in the first matrix is 3 and the number of columns in the second matrix is also 3. So, the output array will have a dimension of 3×3.
Now follow the steps below:
❶ Select 9 consecutive cells having 3 rows and 3 columns.
❷ Enter the following formula to the top-left corner of the selection area.
❸ To execute the formula, hit the CTRL + SHIFT + ENTER buttons altogether.
In Microsoft Office 365, instead of following the above steps, just insert the formula in cell B10 and press the ENTER button. The dynamic array formula will automatically fit the necessary area.
Example 6: Use SUM, MMULT, TRANSPOSE, and COLUMN Functions to Count the Number of Rows Having a Specific Value
This time we will count the total number of rows having the number 5. In this regard, one problem may arise. That is the number 5 can be present in more than one column.
So, we have to ensure that any being present in more than one column should be counted as only 1.
To do this we have used the SUM, MMULT, TRANSPOSE, and the COLUMN function to build a formula that will eliminate this problem and count only the number of rows having a specific number present in them.
Now follow the steps below to do that.
❶ Insert the following formula in cell D16.
❷ Press the CTRL + SHIFT + ENTER button to execute the formula.
If you are a Microsoft Office 365 user, then just press the ENTER button instead of pressing CTRL + SHIFT + ENTER buttons altogether.
Things to Remember
📌 The number of columns in array1 must be the same as the number of rows in array2.
📌 If the cells are blank or contain any text, then the MMULT function returns a #VALUE error.
📌 The MMULT function also throws a #VALUE error, if the numbers of columns in array1 and the numbers of rows in array2 are mismatched.
To sum up, we have discussed 6 examples to guide you in using the MMULT function in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website ExcelDemy to explore more.