How to Use the MMULT Function in Google Sheets

The MMULT function in Google Sheets is short for “matrix multiplication function” and it automates row-to-column multiplication for you

Basically, matrix multiplication crosses two matrices to make one matrix. It adds the product of the rows in the first matrix and the corresponding columns in the second matrix. It sounds a bit complicated now, but don’t worry, this will be explained with more depth throughout this article.

Just remember the rule that there should be the same number of columns on the first matrix as there are rows on the second one in order to do the matrix multiplication function.

Let’s take an example.

Say I was part of a management team running this large company that gets sales from multiple countries. I’d have a table with updated exchange rates as well as a table with total sales from our divisions abroad in their respective currencies. 

With those two tables and just one function in Google Sheets, I can get the total sales converted to the currency of our main division.

The MMULT function is useful for various practical use cases, especially when paired with other Google Sheets formulas. But for this guide, we’ll keep it simple and straightforward. This means you’d be flexible with formatting your matrices to cater to the format of the function.

Grading classes, for example, should be made easier for teachers using the MMULT function if they are willing to write out two tables in a certain way. First, they make a table with the weightage of each grading criteria at length horizontally and use that as the first matrix. Then, the table of grades wherein each student is a column label serves as the second matrix.

Perhaps that’s a bit difficult to visualize. We’ll run down how to use the MMULT function in Google Docs step by step, with a real-world example. Let’s begin!

 

 

The Anatomy of the MMULT Function in Google Sheets

So the syntax (the way we write) the MMULT function is as follows:

=MMULT(matrix1, matrix2)

Let’s dissect this thing and understand what each of these terms means:

  • = the equal sign is just how we start any function in Google Sheets
  • MMULT() is our matrix multiplication function.
  • matrix1 is the array or range of data that serves as our first matrix.
  • matrix2 is the array or range of data that serves as our second matrix.

As mentioned before, matrix1 has to have an equal number of columns as rows in matrix2.

Also, let’s clarify what an array is. It is the arrangement of values in your table written with a colon between each value in consecutive then a semicolon to separate them by row. Then, a bracket goes on both ends. In ={1,2;3,4} for example, 1 would be beside 2, 3 would be below 1, and 4 would be below 2.

It would be easier to use the data range like A1:E4 as the matrix for this function though, so we’ll be using that in this guide.

 

 

A Real Example of Using the MMULT Function in Google Sheets

Take a look at the example below to see how to use the MMULT function in Google Sheets.

As you can see, I’ve run with the case scenario I presented in the introduction of this guide. A multinational company has outlined the exchange rates from their different divisions on one table and the total sales in the local currency of each division on the other. The first matrix has 4 columns and 2 rows, while the second matrix has 5 columns and 4 rows.

To get the Total Sales in the currency of the main division, US Dollars, I will have to multiply the Exchange Rates by the sales of each unique product sold in the foreign currency. Manually, this would take some time and effort, but with the MMULT function, the output matrix (Total Sales) is generated in just a few seconds.

The values were calculated this way:

  • A = 4500*0.27 + 9700*0.73 + 3200*0.13 + 58000*0.014 = 9524
  • B = 5700*0.27 + 1100*0.73 + 1700*0.13 + 73000*0.014 = 3585
  • C = 3900*0.27 + 6500*0.73 + 9000*0.13 + 52000*0.014 = 7696
  • D = 8000*0.27 + 4700*0.73 + 6400*0.13 + 81000*0.014 = 7557
  • E = 7200*0.27 + 4733*0.73 + 5900*0.13 + 64000*0.014 = 6016

You’ll notice that the values on the rows of the first matrix are multiplied by the values on the columns of the second matrix, left to right and up to down respectively.

To make this guide easier to follow through, you can go ahead and make a copy of the spreadsheet I made with the link attached below.

At this point, we will now get into the steps of using the MMULT function given the example situation I have presented.

 

 

How to Use the MMULT Function in Google Sheets

1. First, you’ll have to click on the first cell of the row where you want your output matrix to be. Later, the rest of the values will fill in the row.

Choose a Cell

2. Now, type in the equal sign ‘=’ to begin the function and follow it with ‘mmult’ or ‘MMULT’. Either way, an automatic pop-up should show recognizing the function.

Write Beginning of MMULT Function

3. Then, press Tab to accept as instructed on the pop-up. You are now supposed to choose your matrix1. So, click and drag through the cell range accordingly. In this case, that’s B2:E2.

MMULT Function in Google Sheets

4. At this point, you can now select the data range for matrix2. But first, separate matrix1 with a comma. Then you can now click and drag through the cells accordingly. In this case, that’s B5:F8.

MMULT Function in Google Sheets

5. Finally, press the Enter key. You don’t even have to close out your function with the close parenthesis since Google Sheets will automatically do that for you. Your output matrix or what some would call ‘third matrix’ is now up on your spreadsheet.

MMULT Function in Google Sheets

That’s it! You’ve just used the MMULT Function in Google Sheets. If you want to know more about making a powerful tool out of Google Sheets and even MS Excel, be sure to subscribe to be notified of future posts.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like