What is MMULT function In Google Sheets?
MMULT in Google Sheets helps us calculate the product of two matrices. In other words, the MMULT function is a function that multiplies two matrices together which is an otherwise tedious process, especially when done manually. It returns the product as a new matrix. To use the MMULT function, there must be two ranges as an argument, each representing the matrix to multiply. Mathematically, matrix multiplication differs from regular number multiplication as we multiply entire rows and columns from two matrices. Let us look at a simple example of matrix multiplication using the MMULT formula in Google Sheets.
Matrix A: Matrix B:
[1 3] [4]
[5 7] [6]
In this example, we have matrix A, 2*2 (called 2 by 2). It means it has 2 rows and 2 columns. Matrix B has 2 rows and 1 column. The number of columns in Matrix A should match the number of rows in B. It is the criteria for using MMULT. Apply the function =MMULT(B1:C2,E1:E2). Press Enter. The resulting matrix will have the same number of rows as Matrix A and the same number of columns as Matrix B.
Key Takeaways
- The MMULT function in Google Sheets performs matrix multiplication, one of the most fundamental operations in linear algebra.
- The syntax of the MMULT function is as follows.
=MMULT(array1, array2).
- The result of MMULT is an array. The number of columns in Matrix 1 should always match the number of rows in Matrix 2.
- Matrix multiplication is non-commutative and widely used in the fields of machine learning, statistics, regression analysis, and economics.
Syntax
Before proceeding with some practical applications with MMULT in Google Sheets, let us look at its formula.
The syntax of the MMULT function is:
=MMULT(matrix1, matrix2)
- matrix1: The first matrix we will include in the multiplication operation
- matrix2: The second matrix present in the multiplication operation
Here, matrix 1 and matrix 2 can represent an array or range. An important point to remember before performing any matrix multiplication is that the number of columns in matrix1 must equal the rows in matrix2. It is a standard rule in any matrix multiplication.
How to Use MMULT function In Google Sheets?
The MMULT formula calculates the matrix product of two matrices specified as arrays or ranges. There are two ways that the function can be used in Google Sheets.
- Manually enter MMULT
- Through the Google Menu Bar
Manually Enter MMULT
Let us first look at how to enter the MMULT function. Here’s how we do it step-by-step:
Step 1: We have entered two matrices, A and B. Let us multiply them using the MMULT function. Look at the image shown below.
Step 2: Click on the cell where you want the result to appear. Let’s use B8 for this example. Type =MMULT( into the cell.
Step 3: Next, select the range for Matrix A. It can be done by clicking and dragging the mouse from A1 to B2. Then, type a comma and select the range for Matrix B. Close the parentheses and hit Enter.
=MMULT(A1:B2, D1:E2)
Step 4: Once you press Enter, the result should appear in the cells starting at B8.
Using the Google Menu Bar
- Choose the cell where you want to enter the formula.
- Go to the menu bar and click on “Insert” ➝ “Function” ➝ “Array ➝ “MMULT.”
Once the function is entered in Google Sheets, enter the required arguments, which are the matrix cell ranges, and press Enter.
Examples
Matrix multiplication is simple to perform using MMULT where the resulting matrix has dimensions based on the number of rows of the first matrix and the number of columns of matrix 2. It is widely used to solve linear systems of equations and coordinate transformations. It is also used in pattern recognition. Let us look at some interesting examples of how to implement this function.
Example #1 – (2×2) by (2×1) Matrix Multiplication
Let’s look at an interesting example of using the MMULT function to analyze sales data. Suppose we have a matrix representing the sales figures of two Barbies across two different stores. We have another matrix representing the cost price of each Barbie. Let us calculate the total cost of the Barbies sold for each store.
Step 1: We have presented the data in the matrix below.
Sales Matrix: Cost Matrix:
[10 20] [5]
[15 25] [6]
Step 2: To calculate the total cost for each store, we use MMULT to multiply the sales matrix by the cost matrix. In cell C8, use the following function.
=MMULT(A1:B2, D1:D2). Press Enter.
Step 3: This formula returns a matrix representing the total sales cost for each store. Such calculations help you make informed decisions based on the cost data on how to stock the Barbies for optimal sale and profit.
Example #2 – (2×2) by (2×3) Matrix Multiplication
Now that we have seen an interesting example above let us show how to perform matrix multiplication between a 2×2 matrix and a 2×3 matrix using the MMULT function. Take a look at the steps below. We need to follow the rules of matrix multiplication.
Step 1: Let us enter matrices A and B in a spreadsheet.
Step 2: Using the MMULT Function in Google Sheets, in a new range, enter the following formula:
=MMULT(A1:B2, D1:F2)
Step 3: Press Enter, and the resulting matrix will appear in the range H1:J2:
It gives you the result of multiplying a 2×2 matrix by a 2×3 matrix. Multiplying this manually would be a tedious process prone to errors. It is where MMULT in Google Sheets comes in handy to ease your calculations. Several complex linear systems can be solved with no trouble!
Example 3 – (3×3) by (3×2) Matrix Multiplication
Let’s go through an example of multiplying a 3×3 matrix by a 3×2 matrix. Let us look at how this can be done simply with MMULT.
Step 1: Enter the following matrices in a spreadsheet.
Matrix A (3×3)
Matrix B (3*2)
Step 2: Now, enter the following function in cell C10.
=MMULT(B1:D3,B6:C8)
Step 3: Press Enter.
When multiplying a 3×3 matrix with a 3×2 matrix, the resulting matrix will have dimensions equal to 3×2. Mathematically, each element is a product of the corresponding row from the first matrix and the corresponding column from the second matrix.
Thus, the resultant array is a 3*2 array. Let’s wrap things up with some important points to be noted.
Important Things to Note
- All the arrays or ranges used for matrix multiplication must contain numbers only, otherwise, MMULT returns the #VALUE! error.
- The most important rule for matrix multiplication is that the columns in Matrix1 must be equal to the rows in Matrix2.
- MMULT returns the #VALUE! error if Matrix1 columns do not equal the Matrix2 rows.
- In Google Sheets, it is enough if you enter the MMULT formula in a single cell. It automatically expands the result to the required size, thereby sparing you the hassle of selecting a range for the result.
Frequently Asked Questions (FAQs)
When you use the MMULT, you are bound to run into some common errors and how to rectify them Let us look at them.
Mismatch in Dimension: If the two matrices don’t match, the number of columns in the first Google Sheets matrix does not match the number of rows in the second one, the #VALUE! Error arises. Before applying the function, double-check this rule to ensure the matrices are compatible with multiplication.
If you have any empty or blank cells, you may again get a #VALUE! Error. Check if all the cells in your matrices contain numbers, which can also be zero.
Since matrices involve bigger and wider ranges, ensure you always select the right range for both.
You can combine MMULT with other functions to perform more complex calculations. Using the MINVERSE and MMULT functions, we can easily solve a system of linear equations by performing matrix inversion. You can use MMULT with TRANSPOSE to change the orientation of a matrix before performing multiplication which is useful when your data needs a different structure.
Before performing the multiplication, check if the number of columns in the first matrix is equal to the number of rows in the second matrix.
Remember that matrix multiplication is non-commutative. Hence, the order of multiplication of matrices matters.
Check if both the arrays contain only numeric values.
Download Template
This article must help understand MMULT Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is MMULT in Google Sheets. We learn its syntax & how to use it for matrix multiplication with examples and working template. You can learn more from the following articles. –
Leave a Reply