How To Use MMULT Function in Google Sheets [Practical Example]

0
123

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 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 there is one real life example to the use of MMULT formula in Google Sheets.

Syntax to Google Sheets MMULT Function

Syntax:

MMULT(matrix1, matrix2)

Here you should take care of the following.

The number of columns in Matrix1 should be equal the number of rows in Matrix2

You should stick to this standard and I’ll explain what’s it.

Learn MATRIX for MMULT

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 values in the Matrix 1 row 1 (there is only one row) with each values in the Matrix 2, column 1. The below screenshot depicts this.

Then repeat the same for column 2, 3 and 4 as below.

1. 3 x 9 + 4 x 7 + 2 x 4 = 63

2. 3 x 7 + 4 x 4 + 2 x 0 = 37

3. 3 x 15 + 4 x 6 + 2 x 3 = 75

This way you can multiply matrices. 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 Inerest:

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)

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’ve the sales quantity of 6 products for the period of 6 months. In the above, I’ve marked the range B4:G9 which is our 6 rows x 6 columnsĀ Matrix 2.

Now 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 is 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. Then you can easily learn the MMULT concept and Matrix.