MMULT Function in excel is an essential function. It can be categorized under Mathematical Functions of Excel. Let us see the usage of this function.
When to use MMULT Function in Excel?
The MMULT Function performs the matrix multiplication of two matrixes in Excel. A matrix is a two-dimensional array of values or a range of cells. The matrix product of two matrixes results in another matrix.
The MMULT Function is an Array Formula of Excel. This is so because it returns an array of values as a matrix product. The MMULT Function result automatically spills into a range of cells.
Condition To Perform Matrix Multiplication
In matrix multiplication, the rows of one matrix are multiplied by the columns of another matrix. A matrix contains a number of rows and columns.
Condition – In order to perform Matrix Multiplication, the number of columns of the first matrix must be equal to the number of rows of the second matrix.
The matrix dimensions are represented by the number of Rows x the Number of Columns.
If this condition is not satisfied then the Matrix Multiplication process is interrupted. In other words, we can say that the MMULT Function returns a #VALUE! error if the number of columns of matrix 1 is not equal to the number of rows of matrix 2.
Syntax and Arguments
The following points will explain to you the required function inputs for the MMULT Function to work.
- array1 – This is a range of cells we need to pass as the elements of the first matrix.
- array2 – This is the 2D array containing the elements of the second matrix.
Both array1 and array2 are mandatory to input as MMULT Function arguments for the function to operate.
Important Points about MMULT Formula
We must be aware of the following points about MMULT Formula in order to use the MMULT Function without bugs.
- MMULT is an array formula. If you have Excel 2019 or an earlier version of Excel, then after typing the MMULT formula in any cell, you need to press the Ctrl Shift Enter key to get the spilled matrix product result. You cannot press only Enter key as this would not give matrix product results.
- For MS Excel 365 or Excel 2021, you can press the Enter key to get spilled results. The MMULT formula works the same as any other formula in newer versions of Excel.
- The MMULT Function would return a #VALUE error in two cases:-
- You have passed the text string or a Logical Value (TRUE or FALSE) into the array input of the MMULT Function argument. This is invalid as in the Matrix Multiplication we can perform matrix multiplication on numerical values only.
- The matrix dimensions do not specify the condition.
- The MMULT formula will return a #SPILL error if the function results do not get enough empty range of cells to spill into. In this case, you need to delete the cell contents for the MMULT function result to occupy that space.
Example to Perform Matrix Multiplication using MMULT Function in Excel
Let us suppose we have the following two matrices in the range A2:B3 and D2:E3.
Use the following MMULT formula in cell B6 to get the product of Matrix 1 and Matrix 2.
Press the ctrl shift enter key after typing the formula in cell B6.
As a result, the formula has returned the product of matrices. This can be verified as:-
Explanation – We have passed the range A2:B3 as the array1 argument. So this becomes the first matrix for multiplying, The Range D2:E3 is passed as array2 argument. This becomes the second matrix. Once we hit the Ctrl Shift Enter key after typing the formula in cell B6, the matrix product result gets spilled into the range B6:C7.
This brings us to the end of blog,
Thank you for reading.