The MMULT function in Excel is used for matrix multiplication. This functions takes two arrays, or matrices, as inputs and outputs the matrix product of the two. Confused?
Let’s break down this high school math statement by first understanding the syntax.
A matrix product can only be calculated when the number of columns in matrix 1(Array 1) is equal to the number of rows in matrix 2 (Array 2). Therefore, the size of the resulting matrix is determined by the input matrices. If your matrix 1 has A rows and B columns, matrix 2 must have B rows, but could have any number, C, columns. As a result, the matrix would be A rows by C columns. This will make more sense below.
MMULT Function Matrix Setup
In math, matrix multiplication starts by finding the dot product. Quick review on Matrix multiplication. The Dot Product is the result of multiplying the first row from A by the first column of B.
Due to the fact that we multiply the rows (2 rows) of matrix A by the columns of matrix B (2 columns), the resulting matrix C will have a size of 2 x 2. When entering the formula into cell L4, Excel will default to only showing the single Dot product.
In order for Excel to display the complete matrix calculation, you have to hit “Control + Shift + Enter.” This will put brackets around the formula making it into an array.
- All cells must be numbers.
- Number of columns in Array 1, must equal number of columns in Array 2.
- In our example, 2 rows in Array 1 equals 2 columns in Array 2.
- No cells can remain blank.
- Must hit “Control + Shift + Enter” to return full result.