Excel MMULT Function

What is MMULT Function in Excel?

The MMULT function is one of the math functions of Excel.

It Returns the matrix product of two arrays, an array with the same number of rows as array1 and columns as array2.

We can find this function in Math & trig category of insert function Tab.

How to use MMULT function in excel

1. Click on an empty cell (like F5 ).

2. Click on fx icon (or press shift+F3).

3. In the insert function tab you will see all functions.

4. Select math and trig category.

5. Select MMULT function.

6. Then select ok.

7. In the function arguments Tab you will see MMULT function.

8. Array1 is the first array of numbers to multiply and must have the same number of columns as Array2 has rows.

9. Array2 is the second array of numbers to multiply and must have the same number of columns as Array1 has rows.

10. You will see results in the formula result section.

Examples of MMULT function in Excel

1. Simple matrix multiplication: =MMULT(A1:B2,D1:E2)

This formula multiplies two 2×2 matrices together.

2. Using an array formula for matrix multiplication: {=MMULT(A1:B2,D1:E2)}

This is an array formula that performs the same calculation as Example 1, but it can be entered into a range of cells at once.

3. Multiplying a matrix by its inverse: =MMULT(A1:B2,MINVERSE(C1:D2))

This formula calculates the product of a 2×2 matrix A and its inverse matrix C.

4. Finding the dot product of two vectors: =MMULT(A1:A2,B1:B2)

This formula calculates the dot product of two 1×2 vectors.

5. Calculating the covariance matrix of a set of data: =MMULT(TRANSPOSE(A1:C3),A1:C3)/2

This formula uses the TRANSPOSE function to calculate the covariance matrix of a set of data stored in cells A1:C3.

6. Performing a linear regression with matrix algebra: =MMULT(MMULT(MINVERSE(MMULT(TRANSPOSE(A1:C5),A1:C5)),TRANSPOSE(A1:C5)),D1:D5)

This formula performs a linear regression on a set of data using matrix algebra.

7. Finding the sum of squares of residuals: =MMULT(TRANSPOSE(D1:D5-B1:B5),D1:D5-B1:B5)

This formula finds the sum of squares of residuals from a linear regression.

8. Multiplying a 3×2 matrix by a 2×2 matrix: =MMULT(A1:C3,D1:E2)

This formula multiplies a 3×2 matrix by a 2×2 matrix.

9. Performing a matrix multiplication between three matrices: =MMULT(MMULT(A1:B2,C1:D2),E1:F2)

This formula multiplies three matrices together.

10. Calculating the product of two 3×3 diagonal matrices: =MMULT(A1:C3,D1:F3)

This formula multiplies two diagonal matrices together.

Example 1:

How to use MMULT function in excel

You can see examples of MMULT function below:

=MMULT({1,2;3,4},{5,6;7,8})------->>>> answer is {19,22;43,50}
=MMULT({1,0;0,1},{0,1;1,0})------->>>> answer is {0,1;1,0}
=MMULT({1,2;2,1},{2,1;1,2})------->>>> answer is {4,5;5,4}

Example 2:

How to count rows with specific value?

By using MMULT function, we can count a specific record in the table.

For example, in the table below, How many people weigh more than 230 kg?

=SUM(--(D2:D10>230))---->>>>>answer is 5

For example, in the table above, How many people weigh or Height is more than 200 kg?

=SUM(--(MMULT(--(B2:E10>200),TRANSPOSE(COLUMN(B2:E10)))>0))
answer is 8

Errors in MMULT function

MMULT returns the #VALUE! error when:

1. Any cells are empty or contain text

=MMULT({,2;3,4},{5,6;7,8})------->>>> answer is #VALUE! 
=MMULT({1,0;0,},{0,1;1,0})------->>>> answer is #VALUE! 
=MMULT({1,2;,1},{2,1;1,2})------->>>> answer is #VALUE! 

2. The number of columns in array1 is different from the number of rows in array2.

=MMULT(A3:B4,D3:E3)------->>>> answer is #VALUE! 
=MMULT(A6:A7,D6:E7)------->>>> answer is #VALUE! 
=MMULT(A9:B10,D9:E11)------->>>> answer is #VALUE! 

Excel’s MMULT function simplifies matrix multiplication

Excel’s MMULT function is an extremely useful tool for performing matrix multiplication. This function allows you to multiply two matrices together, which can be very time-consuming if done manually.

For example, suppose you have two matrices:

Matrix A:     Matrix B:
1 2           3 4
3 4           5 6

To multiply these two matrices together using the MMULT function, you simply need to enter the following formula into a cell:

=MMULT(A1:B2, C1:D2)

This will return the resulting matrix of the multiplication operation.

What the MMULT acronym stands for in Excel

The MMULT acronym stands for “matrix multiplication”. As the name suggests, the MMULT function is used to perform matrix multiplication in Excel.

Understanding the arguments of the MMULT function

The MMULT function takes two required arguments: the first matrix and the second matrix. These matrices must have the same number of columns in the first matrix as there are rows in the second matrix. In other words, if matrix A has m columns, then matrix B must have m rows.

For example, if we want to multiply matrix A (which has 2 columns) by matrix B (which has 2 rows), we would use the following formula:

=MMULT(A1:B2, C1:D2)

In addition to these required arguments, the MMULT function can take an optional third argument: the transpose of the first matrix. This argument is specified using the value “1”.

How to use the MMULT function in Excel: A guide

Using the MMULT function in Excel is straightforward. First, select the cell where you want the result of the multiplication to appear. Then, type the formula for the MMULT function, specifying the two matrices to be multiplied.

For example, suppose we want to multiply matrix A by matrix B, as shown above. We would select a cell where we want the result to appear (e.g., cell E1), and then enter the following formula:

=MMULT(A1:B2, C1:D2)

This will compute the result of the matrix multiplication and display it in the selected cell.

The syntax of the MMULT function in Excel explained

The syntax of the MMULT function is relatively simple. It takes two required arguments, separated by a comma, representing the two matrices to be multiplied. An optional third argument can be specified to transpose the first matrix, by passing the value “1”.

For example, the basic syntax of the MMULT function looks like this:

=MMULT(matrix1, matrix2, [transpose])

Here, “matrix1” and “matrix2” are the two matrices to be multiplied, and “[transpose]” is an optional parameter that specifies whether to transpose the first matrix.

Using the MMULT function with non-square matrices

The MMULT function in Excel works with both square and non-square matrices. However, when using non-square matrices, it is important to ensure that the number of columns in the first matrix matches the number of rows in the second matrix.

For example, suppose we have a 2×3 matrix A:

1 2 3
4 5 6

And a 3×2 matrix B:

7 8
9 10
11 12

To multiply these two matrices together using the MMULT function, we would enter the formula “=MMULT(A1:B2, C1:D3)”.

Multiplying two matrices using the MMULT function

Multiplying two matrices using the MMULT function in Excel involves entering the two matrices as arguments to the function. The MMULT function then multiplies the two matrices together and returns a single result.

For example, if we have two matrices A and B, we can multiply them together using the following formula:

=MMULT(A1:B2, C1:D2)

This will produce a new matrix that is the result of multiplying the two matrices A and B together.

What result to expect from the MMULT function in Excel

The result of the MMULT function in Excel is a new matrix that is the result of multiplying the two input matrices together. The resulting matrix will have the same number of rows as the first matrix and the same number of columns as the second matrix.

For example, if we have two matrices A and B, and we multiply them together using the MMULT function, the resulting matrix will have the same number of rows as matrix A and the same number of columns as matrix B.

Using arrays with the MMULT function in Excel

The MMULT function in Excel can also be used with arrays, in addition to matrices. However, it is important to ensure that the dimensions of the arrays are compatible before attempting to multiply them together.

For example, suppose we have two arrays:

{1, 2, 3}
{4, 5, 6}

And:

{7, 8}
{9, 10}
{11, 12}

To multiply these two arrays together using the MMULT function in Excel, we would enter the formula “=MMULT(A1:B2, C1:D3)”.

How many matrices can be multiplied at once with the MMULT function?

The MMULT function in Excel can only multiply two matrices at a time. If you need to multiply more than two matrices together, you will need to use multiple instances of the MMULT function.

For example, if we want to multiply three matrices A, B, and C together, we would need to perform two separate multiplication operations using the MMULT function:

=MMULT(A1:B2, C1:D2)
=MMULT(result1, E1:F2)

Does the order of matrix multiplication matter in the MMULT function?

Yes, the order of matrix multiplication matters when using the MMULT function in Excel. This is because matrix multiplication is not commutative, meaning that the order in which matrices are multiplied can affect the result.

For example, if we have two matrices A and B, multiplying A by B will give a different result than multiplying B by A. Therefore, it is important to pay attention to the order of multiplication when using the MMULT function.

Solving systems of equations with the MMULT function in Excel

The MMULT function in Excel can be used to solve systems of linear equations by representing the system of equations as a matrix equation. This involves setting up a matrix of coefficients for the variables in the equation, and then using the MMULT function to solve for the variables.

For example, suppose we have the following system of linear equations:

2x + 3y = 10
4x + 5y = 20

We can represent this system as a matrix equation:

| 2 3 |   | x |   | 10 |
| 4 5 | * | y | = | 20 |

Solving for x and y involves computing the inverse of the matrix of coefficients, and then multiplying the inverse by the matrix of constants. This can be done using the MMULT function in Excel.

Using complex numbers with the MMULT function

The MMULT function in Excel supports complex numbers, so it is possible to perform matrix multiplication with complex matrices. Complex numbers are represented in Excel using the “i” suffix.

For example, suppose we have two complex matrices:

Matrix A:     Matrix B:
1+2i 3-4i     5+6i 7-8i
4+5i 6-7i     9+10i 11-12i

To multiply these two matrices together, we would use the following formula:

=MMULT(A1:B2, C1:D2)

This will return the resulting complex matrix of the multiplication operation.

Matrix inversion with the MMULT function in Excel

Matrix inversion is a common operation when working with matrices, and it can be done using the MMULT function in Excel. To invert a matrix, you need to first calculate its determinant, and then use this value to find the inverse of the matrix.

For example, suppose we have the following matrix A:

| 2 3 |
| 4 5 |

To invert this matrix using the MMULT function in Excel, we would first calculate its determinant:

=DET(A1:B2) = -2

We can then use this value to compute the inverse of the matrix:

=MMULT(MINVERSE(A1:B2), -1/2)

This will return the inverse of the matrix A.

Avoiding #VALUE! errors when using the MMULT function in Excel

One common error that can occur when using the MMULT function in Excel is the #VALUE! error. This error occurs when the dimensions of the matrices being multiplied are not compatible.

To avoid this error, it is important to ensure that the number of columns in the first matrix matches the number of rows in the second matrix. If the dimensions are not compatible, the MMULT function will not be able to perform the multiplication operation and will return a #VALUE! error.

Handling empty cells in a matrix with the MMULT function

When working with matrices in Excel, it is possible to encounter empty cells. These empty cells can cause problems when using the MMULT function, as they can lead to incorrect results or errors.

To handle empty cells in a matrix with the MMULT function, you can use the IF function to replace the empty cells with zeros. This ensures that all cells in the matrix have a value, and allows the MMULT function to work correctly.

For example, suppose we have the following two matrices:

Matrix A:     Matrix B:
1   2         3   4
    5         6    
7   8            

To multiply these two matrices together using the MMULT function, we would first use the IF function to replace any empty cells with zeros:

=MMULT(IF(A1:B3<>"", A1:B3, 0), IF(C1:D2<>"", C1:D2, 0))

This will ensure that all cells in both matrices have a value, and allow the MMULT function to produce the correct result.

Comparing the MMULT and PRODUCT functions in Excel

The MMULT and PRODUCT functions in Excel are both used for matrix multiplication, but they work in different ways. The MMULT function is used to multiply two matrices together, while the PRODUCT function is used to multiply the values in a range of cells.

For example, suppose we have the following two matrices:

Matrix A:     Matrix B:
1 2           3 4
3 4           5 6

To multiply these two matrices together using the MMULT function, we would use the following formula:

=MMULT(A1:B2, C1:D2)

To multiply the values in a range of cells using the PRODUCT function, we would use a formula like this:

=PRODUCT(A1:B2)

This would multiply all of the values in the range A1:B2 together.

Conditional formatting with the MMULT function

Conditional formatting in Excel allows you to apply formatting to cells based on their values. This can be useful when working with matrices and the MMULT function, as it can help you identify patterns or outliers in your data.

For example, suppose we have a matrix that we want to highlight any cells containing a value greater than 10. We can do this using conditional formatting by selecting the matrix, clicking “Conditional Formatting” in the “Home” tab, and then selecting “Highlight Cells Rules” -> “Greater Than”. We can then enter the value “10” and select a formatting option, such as changing the cell’s background color.

Availability of the MMULT function in different versions of Excel

The MMULT function is available in most versions of Excel, including Excel 2007, Excel 2010, Excel 2013, Excel 2016, and Excel 2019. It is also available in Office 365 and Excel for Mac.

However, some older versions of Excel may not include the MMULT function. In these cases, it may be necessary to upgrade to a newer version of Excel or to use a different software tool for matrix multiplication.

Limitations of the MMULT function in Excel

While the MMULT function in Excel is very powerful, it does have some limitations. One limitation is that it can only multiply two matrices at a time. If you need to multiply more than two matrices together, you will need to use multiple instances of the MMULT function.

Another limitation is that the MMULT function can only perform matrix multiplication. It cannot perform other operations, such as matrix addition or subtraction. To perform these operations, you will need to use other functions or tools.

• PRODUCT function
• MUNIT function
• MINVERSE function
• MDETERM function
• SUMPRODUCT function

Related posts:

TRANSPOSE Function in Excel Excel MINVERSE Function Excel MDETERM Function Excel PRODUCT Function