If you need to multiply matrices, the MMULT function is an easy way to do matrix multiplication.
In this tutorial, I will show you how to use the MMULT function in Google Sheets.
The MMULT function will calculate the matrix product of two matrices that are given as arrays or ranges.
The syntax of the MMULT function is:
- matrix1 – this is the first matrix to be multiplied. It has to be an array or range
- matrix2 – this is the second matrix to be multiplied. It has to be an array or range
The number of columns in matrix1 has to be equal to the number of rows in matrix2. This is standard for doing matrix multiplication.
Using the MMULT Function
In this example, I have two matrices that I will multiply together to create a third matrix using the MMULT function.
Here are the steps to do this:
1. First select the cell where you will be entering the formula. Type an equals sign (=) and type MMULT and then press Tab to begin entering into the formula
2. Inside the formula select the cell range that contains your first row of matrix 1, then place a comma after this cell range
3. Next select the first column in matrix 2 that will be multiplied by the first row in matrix 1. In my example this is A6:A7.
4. Place your closing parenthesis at the end of the formula and press Enter on your keyboard to calculate the function. In my example, the value returned here is 21. This is because (1×5) + (2×8) = 21
5. If you want to continue multiplying your matrices together you can repeat the process. Now I am taking the second row of matrix 1 (A3:B3) and multiplying it by the same first column in matrix 2. This is equal to 47 in my example because (3×5) + (4×8) = 45
6. Continue entering the formula as needed. Here I am now on the second column of matrix 3. Here I will be multiplying the first row of matrix 1, with the second column in matrix 2
7. The formula is created in every cell until I have all my values entered into the new matrix
In the above example, I used the MMULT function six times for each cell in matrix 3. You can actually do all of this in one formula. I just wanted to show you how the function works by doing each cell manually. To do one formula, you will simply put the entire range of both matrices into your function.
So your formula would be this:
Here is how this looks in a spreadsheet:
If you are regularly multiplying data from different tables and arrays together in Google Sheets, there are certainly cases where the MMULT function might come in handy.
The key is to learn and understand how the function works. Then, when you are comfortable with using this formula, you will be able to use it in your own spreadsheet work to speed up your productivity.
A common example of this formula that might be used in a real-life scenario is to take a matrix of sales quantity and multiply it by a matrix of the sales price. Then you would get sales revenue in your third matrix.
More Google Sheets Tutorials:
How to Use the RANDARRAY Function