This guide will explain how to perform matrix multiplication in Excel using the MMULT function.
The rules for using the MMULT function in Excel are the following:
- The
MMULTfunction is used to return the matrix product of two arrays. - The arrays must contain numbers only.
- Columns in array1 must equal the rows in array2. So we can only multiply our two matrices if the number of rows in array2 matches the number of columns in array1.
- When any cells in array1 and array2 contain non-numeric values, the
MMULTfunction returns the #VALUE! error. - When array1 columns do not equal array2 rows, the
MMULTfunction returns the #VALUE! error.
Since it has several built-in functions and tools, Excel is a powerful tool to use for different purposes and situations. For example, we can easily calculate or perform complex calculations in Excel using the built-in functions.
In this guide, we will learn how to perform matrix multiplication in Excel by simply using the MMULT function. So the MMULT function is a built-in function in Excel that will perform matrix multiplication.
So we do not have to undergo the long process manually. And we can easily and quickly apply the MMULT function to get the matrix product of two arrays. Furthermore, this will also minimize the errors that we may make when manually performing the long calculation.
Let’s take a sample scenario wherein we must perform matrix multiplication in Excel.
Suppose you have two matrices in your data set. And you want to perform matrix multiplication to get the dot product from the two matrices. To do this, you simply used the MMULT function to easily and quickly get the dot product.
Afterward, you obtain a product that is an array with the same number of rows from the first matrix or array1 and the same number of columns from the second matrix or array2.
Before we move on to a real example of performing matrix multiplication in Excel, let’s first learn the syntax of the MMULT function.
The Anatomy of the MMULT Function
The syntax or the way we write the MMULT function is as follows:
=MMULT(array1, array2)
Let’s take apart this formula and understand what each term means:
- = the equal sign is how we activate any function in Excel.
- MMULT() is our
MMULTfunction. And this function is used to return the matrix product of two arrays. So the result is an array with the same number of rows as array1 and the same number of columns as array2. - array1 is a required argument. So this refers to the first array of numbers to multiply. Additionally, this must have the same number of columns as array2 has rows.
- array2 is also a required argument. And this refers to the second array we want to multiply. Similarly, this array must have the same number of rows as array2 has columns.
Great! Now we can dive into a real example of performing matrix multiplication in Excel using the MMULT function.
A Real Example of Performing Matrix Multiplication in Excel
Let’s say we have a data set containing two matrices. So the first matrix has 2 rows and 3 columns. On the other hand, the second matrix has 3 rows and 2 columns. And we want to perform matrix multiplication. So our initial data set would look like this:
So matrix multiplication is a useful mathematical operation that calculates the product of two matrices. And the result or product of matrix multiplication is an array that has the same number of rows as array1 and the same number of columns as array2. Also, the result is sometimes called the dot product.
For instance, array1 has 2 rows and 3 columns (2 x 3), while array2 has 3 rows and 2 columns (3 x 2). Thus, the result or dot product would have 2 rows and 2 columns (2 x 2).
Before we explain how to use the MMULT function to perform matrix multiplication in Excel, let’s first learn how matrix multiplication works. Firstly, the dot product or resulting array is obtained by multiplying the numbers in the first rows of array1 by the values in the first column of array2.
Then, the products are summed together. So the single value from this process becomes the first entry or value in the first row and first column of the dot product or resulting array.
Afterward, the process is repeated until we find the dot product for each row and column combination. In this example, it would be until we have 2 rows and 2 columns.
However, this is much easier to perform in Excel since we can simply utilize the MMULT function. So the MMULT function will perform the calculations and simply return a result from the matrix multiplication.
So we simply use a formula containing the MMULT function. Then, we will select our matrices to input as array1 and array2. Lastly, the function will return the result. So our final data set would look like this:
You can make your own copy of the spreadsheet above using the link attached below.
Amazing! Now we can proceed and explain the process of how to perform matrix multiplication in Excel using the MMULT function.
How to Perform Matrix Multiplication in Excel
In this section, we will explain the step-by-step process of how to perform matrix multiplication in Excel using the MMULT function. Furthermore, each step has detailed instructions and pictures to make the process easier to follow.
To apply this method to your work, we can simply follow the steps below.
1. Firstly, we need to decide where we want to input the results from the matrix multiplication. Afterward, we can simply type in the formula “=MMULT(B2:D3,F2:G4)”. Lastly, we will press the Enter key to return the result.
2. And tada! We have successfully performed matrix multiplication using the MMULT function.
And that’s pretty much it! We have successfully explained how to perform matrix multiplication in Excel using the MMULT function. Now you can apply this method whenever you need to do matrix multiplication.
Are you interested in learning more about what Excel can do? You can now use the MMULT function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
