Here we can learn A to Z about Google Sheets MMULT function. This’s an array function which is related to multiplying Matrices. So let us learn what is Matrix and how to use MMULT function in Google Sheets to multiply Matrices.
First, let me take you to the syntax part of this spreadsheet formula. After that, take time to understand what is math’s matrix with the help of an example.
Finally, I have included one real-life example to the use of MMULT formula in Google Sheets.
Syntax to Google Sheets MMULT Function
Syntax:
MMULT(matrix1, matrix2)
matrix1 – The first matrix in the matrix multiplication operation. It must be represented as an array/range.
matrix2 – The second matrix in the matrix multiplication operation. It must be represented as an array/range.
Here you should take care of the following. Please stick to the below standard and I’ll explain what it is related to.
The number of columns in Matrix1 should be equal to the number of rows in Matrix2
Learn the MATRIX for MMULT in Google Sheets
See two Matrices below which follows the above standard. Here the number of columns in the Matrix 1 is 3 which is equal to the number of rows in the second Matrix.
Now let us see how to multiply the above two Matrices.
There are four steps involved. First multiply each value in the Matrix 1 row 1 (there is only one row) with each value in the Matrix 2, column 1.
The below screenshot depicts this.
Then repeat the same for column 2,
3 x 9 + 4 x 7 + 2 x 4 = 63
colum 3,
3 x 7 + 4 x 4 + 2 x 0 = 37
and column 4.
3 x 15 + 4 x 6 + 2 x 3 = 75
This way you can multiply matrices. In real-life this will come useful. I’ll come to that later.
Now it’s time to automate the Matrix multiplication with the help of Google Sheets MMULT formula. Here we go!
How To Use MMULT Function in Google Sheets
If you could understand the above Matrix multiplication concept, using MMULT is kids play. Just select the range containing Matrix1 and Matrix2 and separate it with Comma in MMULT.
Let’s see the MMULT formula for the above two Matrices. You can see that the following MMULT formula result is matching with our above manual calculation.
Point of Interest:
The MMULT formula result will obviously retain the same number of rows as per the matrix 1, and the same number of columns as per the matrix 2.
Here see the first value, i.e. 83, in the result. It’s the multiplication of the values 3 x 13 + 4 x 8 + 2 x 6.
If you know Google Sheets TRANSPOSE as well as SUMPRODUCT functions, then you can easily understand the below formula.
The below Sumproduct comparison to MMULT formula would return the value 83.
=SUMPRODUCT(transpose(B3:D3),G4:G6)
Once again understand the MMULT formula usage with the help of this image. In both examples, matrix 1 in cyan color and matrix 2 in green color, see how the multiplication operation has taken place.
Practical Example to the Use of MMULT Function in Google Sheets
Here is one MMULT formula to make you understand why MMULT is a necessary function in Google Sheets. It’s a real-life example.
Sample Data:
Here I have got the sales quantities of 6 products for a period of 6 months. In the above, I’ve marked the range B4:G9 which is our 6 rows x 6 columns Matrix 2.
I have the following rates for the 6 products spread across the range B16:G16. You can consider this range as our 1-row x 6 columns Matrix 1.
Here you may please take a note that the 6 columns in Matrix 1 are matching with 6 rows in Matrix 2. That means the two arrays follow the Matrix standard. It happens naturally.
We can use here Google Sheets MMULT formula to find the month wise sales value of all the products.
Follow this tutorial by applying the above examples in a Google Sheets file. Then you can easily learn the MMULT concept and Matrix.
Related Tutorials: