The MMULT function in Google Sheets provides a really easy way to perform matrix multiplication. In this tutorial we will discuss how matrix multiplication works and how the MMULT function can help you get it done quickly and easily. We will also take a look at a use-case to understand how useful the MMULT function can be.
This Article Covers:
How Matrix Multiplication Works
Matrix multiplication is the process of creating a single matrix from two different matrices. The process consists of row-by-column multiplication, where values in one row of the first matrix are multiplied by the corresponding values in a column of the second matrix, after which these products are all summed up to obtain a single value of the result matrix.
An important rule in matrix multiplication is that the number of columns in the first matrix should always be equal to the number of rows in the second matrix. The resultant matrix always contains the same number of rows as the first matrix and the same number of columns as the second matrix.
For example, if the first matrix, Matrix 1Â has 3 rows and 2 columns, while the second matrix, Matrix 2Â has 2 rows and 1 column, the resultant matrix obtained after matrix multiplication will have 3 rows and 1 column.
Result after multiplying Matrix 1 an Matrix 2:
A table of numeric values can also be considered as a matrix. Hence, two tables, where the number of columns in the first matrix is equal to the number of rows in the second matrix, can also be multiplied using matrix multiplication.
To understand this a little more clearly, consider the following two tables. Let’s say the first table is Matrix 1 and the second table is Matrix 2.
Say we want to perform matrix multiplication of Matrix 1Â and Matrix 2Â and store the result in Matrix 3.
Now notice that Matrix 1Â has 5 rows and 2 columns, while Matrix 2 has 2 rows and 2 columns. So the final matrix, Matrix3 should have 5 rows and 2 columns.
To perform matrix multiplication with these two tables, here’s what we need to do:
- Multiply each value of the first row of Matrix 1 with each value of the first column of Matrix 2. You get: 1 x 2 and 2 x 3
- Sum up these products. This will give the value in row 1, column 1 of the result matrix, Matrix 3. So the value in cell B11 will be =1 x 2 + 2 x 3 = 8.
- Similarly, multiply each value of the second row of Matrix 1 with each value of the first column of Matrix 2. You get: 3 x 2 and 4 x 3.
- Sum up these products. This will give the value in row 2, column 1 of the result matrix, Matrix 3. So the value in cell B12 will be =3 x 2 + 4 x 3 = 18.
- Repeat the same for rows 3, 4 and 5. You should now have all values of the first column of Matrix 3Â filled up.
- To get the values of the second column of Matrix 3, repeat steps 1 to 5, but this time multiplying each row of Matrix 1 with the second column of Matrix 2.
In this way, you can use matrix multiplication to obtain all the values for Matrix 3.
Here’s the final result that we get after matrix multiplication:
Using MMULT in Google Sheets for Matrix Multiplication
As we saw in the previous section, matrix multiplication is not easy to perform manually. Imagine if these were really large matrices with thousands of rows and columns!
Fortunately, Google Sheets provides the MMULT function to completely automate the process, so that all you need to do is provide the cell ranges of Matrix 1 and Matrix 2 values.
Syntax for the Google Sheets MMULT Function
The syntax for the MMULT function is as follows:
MMULT(matrix1, matrix2)
Here,
- matrix1 is the first matrix to be used in matrix multiplication.
- matrix2 is the second matrix to be used in matrix multiplication.
Both matrix1 and matrix2 parameters can be specified by a cell range or an array.
As mentioned before, it is important to ensure that the number of columns in matrix1 should be equal to the number of rows in matrix2.
Using MMULT function, the matrix multiplication explained in the previous section can be performed in a jiffy as follows:
=MMULT(A2:B6,D2,E4)
Here’s the result we get after applying the function.
As you can see, we get the same result but with the least amount of effort.
You can also use an array formula instead of cell references. For example, if you want to multiply a 5 x 2 array with another 2 x 2 array, then you can specify the array as parameters to the MMULT function as follows:
=MMULT({1,2;3,4;5,6;7,8;9,0},{2,1;3,4})
A Practical Use-Case for the MMULT Function
Before we wrap up, let us look at a practical example of how matrix multiplication can be applied with MMULT.
Consider the following table (cells A1:D2):
This table contains weightage for each type of school assessment. According to the table, Class tests have a 0.2 weightage (or 20% of the total), Half-yearly exams have a 0.3 weightage (30% of total), while Final exams have a weightage of 0.5 (50% of total).
If we look closely, we can also consider cells B2:D2 as a 1 x 3 matrix (or a matrix with 1 row and 3 columns).
Now let us look at another table in the same worksheet (cells A6:D9):
This table contains scores of different students in the Class test, Half-yearly and Final exams. If you look closely, we can also consider cells B7:D9 as a 3 x 3 matrix.
To obtain the total scores of the students, we would need to multiply each exam score for each student with the weightage of the corresponding exam and then sum up the products. But an easier way to accomplish this is by using matrix multiplication, or MMULT. Here’s how.
Select cell B10 and type in the formula:
=MMULT(B2:D2,B7:D9)
Press the return key.
You should now see total scores for all three students displayed in cells B10:D10.
We were able to get the total weighted scores of all the students in one go because the MMULT function returned a 1 x 3 array containing results for each student.
Another common application of the MMULT function is in finding the dot product of vectors and matrices in data science and analysis.
There are a multitude of other ways in which you can take advantage of the MMULT function. All you need is a little creative insight.
MMULT vs. SUMPRODUCT
The MMULT function can sometimes be used as an alternative to the SUMPRODUCT function. For example, the above problem with exam scores and weightages could also be obtained using a combination of SUMPRODUCT and TRANSPOSE functions as follows:
- Select cell B10.
- Type the formula: =SUMPRODUCT(TRANSPOSE($B$2:$D$2),B7:B9)
- Press the return key
- Drag the fill handle of the cell to the right, to copy the formula to the rest two cells.
As we can see from the screenshot above, we get the same result as before. We used the TRANSPOSE function to first turn the weightage matrix from a row vector to a column vector, and then applied SUMPRODUCT to find the sum of products of each exam score of a student with the exam weightages.
Note that here, we used SUMPRODUCT to find the total score of each student one-at-a-time, while MMULT got all the student scores in one go.
Since both MMULT and SUMPRODUCT basically involve finding a sum of products, it can be a source of confusion for many. It can make it especially confusing when you are trying to decide on whether you should use SUMPRODUCT or MMULT for a given problem.
The main difference between the two functions is in the types of input ranges they can accept. While SUMPRODUCT only allows input ranges that are equally-sized (or contain the same number of rows and columns), the MMULT function is more flexible.
The MMULT function does not require both input ranges to be equally-sized. All it needs is for the number of columns in the first input range to be equal to the number of rows in the second input range.
Conclusion
In this tutorial we went over the MMULT function, how it works, and how it can be applied in different use-cases. We also explained how it differs from the SUMPRODUCT function, and can sometimes be used as a quicker alternative to the SUMPRODUCT function.
We hope this tutorial helped clarify some of your questions about the Google Sheets MMULT function.
Related:
