Buzz
In our previous article, we introduced readers to basic logical functions in Excel. In this article, Mytour presents the syntax and examples of matrix functions in Excel, including the TRANSPOSE function for returning the transpose of a matrix, the MDETERM function for returning the determinant of a matrix, the MINVERSE function for returning the inverse matrix of a given matrix, and the MMULT function for returning the matrix product of two matrices.
Matrix Functions Used in Excel
Matrix Functions in Excel
The Transpose function returns the transpose of a matrix:
Syntax: TRANSPOSE( array)
Where: Array is an array with an equal number of columns and rows
Example: Given the matrix {1,2;3,4}, find the transpose of the given matrix.
Step 1: Open Excel software and input the given matrix as shown in the image.
Step 2: In cell B10, enter the formula =TRANSPOSE(B6:C7).
Step 3: Select the data range B10:C11, press F2, then press Ctrl+Shift+Enter.
The returned result is the matrix {1.3;2,4} which is the transpose of the matrix {1,2;3,4}.
The MDETERM function returns the determinant of a matrix.
Syntax: MDETERM ( array)
Where: array is an array with an equal number of columns and rows.
Example: Calculate the determinant of the matrix {1,2;3,7}.
Step 1: Open Excel software and input the given matrix as shown in the image.
Step 2: In cell B12, enter the formula =MDETERM(B7:C8) and press enter.
The determinant of the matrix to be found is 1.
The MINVERSE function returns the inverse matrix of a given matrix.
Syntax: MINVERSE( array)
Where: array is an array with an equal number of columns and rows.
Example: Find the inverse matrix of the matrix { 1,2;3,7}.
Step 1: Open Excel software and input the given matrix as shown in the image.
Step 2: Enter the formula =MINVERSE(B7:C8) into cell B12.
Step 3: Select the data range B12:C13, press F2, then press Ctrl+Shift+Enter.
The inverse matrix of the given matrix is {7,-2;-3,1}.
The MMULT function returns the matrix product of two matrices.
Syntax: MMULT( array1, array2)
Where: array1 and array2 are two arrays with the number of columns of array1 equal to the number of rows of array2.
Considering an example: Find the product of the following two matrices:
Matrix 1: {1,1,2;1,1,3;1,1,1} and Matrix 2: {2,2;3,3;1,1}
Step 1: Open Excel software and input the two matrices as shown in the image.
Step 2: Enter the formula = MMULT(A6:C8,E6:F8) into cell B12.
Step 3: Select the data range B12:C14, press F2, then press Ctrl+Shift+Enter to get the result as shown in the image.
The above article has guided you on how to use matrix functions in Excel. The functions MDETERM, MINVERSE, and MMULT are applicable to Office versions 2016, 2013, 2010, 2007, and 2003. The TRANSPOSE function is applicable to Office versions 2010, 2013, and 2016.
