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. Here’s an overview of its basic use.
MMULT Function in Excel: Syntax
- Function Objective:
The MMULT function multiplies two arrays of numbers and returns another array of numbers.
- Syntax:
MMULT(array1, array2)
- Arguments Explanation:
| Argument | Required/Optional | 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
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,
Example 1 – Inserting Number Arrays Manually in the MMULT Function in Excel
- Select the number of cells necessary to get all the cells of the matrix.
- Insert the formula with the MMULT function. For this example, the formula is:
=MMULT({1,2,3;4,5,6},{2,5;4,2;6,9})The starting matrices are 2×3 and 3×2, so the result is a 2×2 matrix, which is why we selected a 2×2 array.
- Press Ctrl + Shift + Enter to execute the formula.
- You will see the curly brackets wrapped up in the formula. This is because the formula is in the form of a legacy array formula.
Note
If you are using Microsoft Office 365, you don’t need to select the range of cells or use CTRL + SHIFT + ENTER because Office 365 supports dynamic array formulas. Use the ENTER button only and the formula will auto-spill to the required number of cells unless it encounters a filled value or a merge.
Example 2 – Using the MMULT Function in Excel to Multiply Two 3×3 Matrices
- Select a range of cells having a measurement of 3×3, as the output matrix dimension will be 3×3.
- Insert the following formula onto the top-left corner of the selection area. Cell B10 for this instance.
=MMULT(B5:D7,F5:H7)The B5:D7 is the range of the first array and F5:H7 is the range of the second array.
- Press the Ctrl + Shift + Enter buttons to execute the formula, unless you’re using Excel 365, in which case you can use Enter.
Example 3 – Calculating the Product of a 2×3 Matrix with a 3×2 Matrix with the MMULT Function
- Select a 2-by-2 array of cells.
- Insert the following legacy array formula to the top-left corner of the selected cells.
=MMULT(B5:D6,F5:G7)- Hit the Ctrl + Shift + Enter buttons to execute the formula.
To do the same task in Excel Office 365, just insert the formula in any cell and press the ENTER button.
Example 4 – Multiplying a 3×2 Matrix with a 2×3 Matrix Using Excel MMULT Function
- Select an area of 3×3.
- Input the following formula in the first cell of the selection area. Cell B10 for this instance.
=MMULT(B5:C7,E5:G6)- Hit the Ctrl + Shift + Enter buttons, or Enter if you’re using Excel 365.
Example 5 – Using the MMULT Function to Multiply a 3×1 Matrix with a 1×3 Matrix
- Select 9 consecutive cells in a 3-by-3 array.
- Enter the following formula to the top-left corner of the selection area.
=MMULT(B5:B7,E5:G5)- Apply with either Ctrl + Shift + Enter or Enter depending on your program version.
Example 6 – Combining SUM, MMULT, TRANSPOSE, and COLUMN Functions to Count the Rows with a Specific Value
We will count the total number of rows that contain the number 5. However, the number 5 can be present in more than one column of the row.
- Insert the following formula in cell D16.
=SUM(--(MMULT(--(B5:D14=5), TRANSPOSE(COLUMN(B5:D14)))>0))- Apply with either Ctrl + Shift + Enter or Enter depending on your program version (older versions or Excel 365).
The formula uses matrix multiplication and converting all cell values to TRUE/FALSE (which are then converted to 1/0 via the double unary minus) depending on whether they are equal to 5 and if they are a number. The SUM function then sums all values of the array or matrix.
Things to Remember
- The number of columns in array1 must be the same as the number of rows in array2. You’ll get a VALUE error otherwise.
- If the cells are blank or contain any text, the MMULT function returns a #VALUE error. You can use matrix operations to convert values beforehand.
Download the Practice Workbook
<< Go Back to Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
What is the feature in MMULT? What does it represent?
Thanks
Hello Roland Sprague,
The MMULT function of Excel represents the matrix multiplication we perform in mathematics. We can use this function if there happens to be two ranges in a sheet representing two matrices and we want to multiply them.
You can find out more about matrix multiplication in this section of the article. It is helpful for computing in linear algebra, transformation of coordinate systems, population modeling, etc.
Regards
Niloy
ExcelDemy