This article will show you various techniques to calculate the inverse matrix in Excel. The Inverse Matrix calculation is a fundamental linear algebraic process, and Excel offers practical tools and functions to carry out this assignment effectively. The ability to undo the effects of a matrix transformation makes the inverse matrix extremely useful for operations like matrix division. Also, the solution of systems of linear equations.
Download Practice Workbook
You can download the practice workbook here.
What Does Inverse Matrix Mean in Excel?
The matrix that is produced when the original matrix is multiplied by the inverse matrix in Excel is known as the inverse matrix. A matrix that “undoes” the effects of the original matrix is known as an inverse of a square matrix.
A matrix’s inverse, represented as A-1, has the following characteristics:
A * A^-1 = A^-1 * A = I,
where the identity matrix I is represented. The Identity Matrix is a square matrix with zeros everywhere else and ones on its major diagonal (from top left to bottom right).
In many mathematical procedures, such as resolving systems of linear equations, carrying out matrix division, and computing determinants, finding the inverse of a matrix is helpful.
How to Calculate Inverse Matrix in Excel: 3 Simple Methods
In this segment, we will briefly discuss 3 simple methods to calculate the Inverse Matrix. We will be using Excel Formula and VBA in the methods. Let’s hover over the methods.
1. Using MINVERSE Function to Calculate Inverse Matrix in Excel
In the first method, we will use the MINVERSE function to get the inverse of a square matrix. We can determine the matrix of any dimension with this function. Let’s see some examples.
1.1 Calculate the Inverse Matrix of a 2×2 Matrix
Simply calculate the inverse matrix of a 2×2 matrix. Let’s use the MINVERSE function.
- Just insert the following formula in a new cell.
In the formula, the range C6:D7 contains a 2×2 matrix. For older versions of Excel, you have to press Ctrl + Shift + Enter instead of Enter because it’s an array formula.
1.2 Calculate the Inverse Matrix of a 3×3 Matrix
Also, you can use the same MINVERSE function formula for determining the inverse matrix of a 3×3 matrix.
- Apply the following formula. Only, the range is adjusted here.
1.3 Calculate the Inverse Matrix of a 4×4 Matrix
You can also do it for a 4×4 matrix.
- Firstly, use the following formula.
2. Using Manual Formula to Determine Inverse Matrix
This time, we will use Excel formulas to determine the Inverse Matrix stepwise. We will calculate the Adjoint Matrix and then divide it by the Determinant of the Matrix to get the Inverse.
- Firstly, apply the following formula in a cell to get the Adjoint Matrix.
=MINVERSE(C6:E8) * (MDETERM(C6:E8) *(-1) ^ (ROW() + COLUMN()))
- Also, in another cell insert the following formula to get Determinant. Let’s use the MDETERM function.
- Afterward, use the following formula in a cell to get the desired inverse matrix. We will simply divide the Adjoint matrix by the Determinant.
3. Creating VBA User-Defined Function to Find the Inverse Matrix in Excel
As with any tasks in Excel a VBA custom function can be created to make an inverse function.
Follow the simple procedures.
- Firstly, Open VBA Window and Insert New Module. Follow the link if you don’t know the way already.
- Then, write the following code in a new module. No need to Run it, you can Reset
Function inverseMatrixA(rng As Range) As Variant Dim matrix() As Variant Dim inverse() As Variant Dim size As Integer Dim i As Integer, j As Integer Dim app As Object size = rng.rows.Count ReDim matrix(1 To size, 1 To size) ReDim inverse(1 To size, 1 To size) ' Convert the range values to a 2D array matrix = rng.Value ' Create an instance of the Excel Application object Set app = CreateObject("Excel.Application") ' Disable alerts and screen updating to speed up the calculation app.DisplayAlerts = False app.ScreenUpdating = False ' Use the MINVERSE function of the Excel Application object to calculate the inverse inverse = app.WorksheetFunction.MInverse(matrix) ' Enable alerts and screen updating app.DisplayAlerts = True app.ScreenUpdating = True ' Clean up and release the Excel Application object Set app = Nothing ' Return the inverse matrix inverseMatrixA = inverse End Function
- The function takes a range (rng) as input and declares variables for the matrices, size, and loop counters.
- The size of the matrix is determined by the number of rows in the input range.
- Arrays (matrix and inverse) are initialized with the appropriate dimensions based on the matrix size.
- The values of the input range are assigned to the matrix array.
- An instance of the Excel Application object (app) is created.
- Alerts and screen updating in Excel are disabled to improve performance.
- The MINVERSE function of the Excel Application object is used to calculate the inverse matrix, and the result is assigned to the inverse array.
- Alerts and screen updating in Excel are enabled again.
- The Excel Application object is released by setting it to Nothing.
- The function returns the inverse matrix as the output.
- Finally, use the custom function in the worksheet to get the inverse matrix.
How to Do Inverse Matrix Multiplication to Find Identity Matrix in Excel?
So far, we have discussed the ways to determine the Inverse Matrix. Let’s find out how to multiply two matrices in Excel. For example, we will multiply a matrix with its inverse matrix to get the Identity Matrix. Follow the procedures.
- Firstly, create a dataset with a matrix. Also, find its inverse matrix.
- Now, use the MMULT function to get the multiplication result which shall be an identity matrix.
In the formula, C6:E8 is the range for a matrix, and C13:E15 is the inverse of the matrix.
Frequently Asked Questions
1. What happens if the matrix cannot be inverted?
Ans: A matrix’s singularity, or non-invertibility, indicates the existence of its inverse. When using the MINVERSE function in Excel to try to find the inverse of a non-invertible matrix, an error such as #VALUE! or #NUM! will appear. When equations are linearly dependent or the matrix is not full rank, non-invertible matrices, which have a determinant of 0, may be used to illustrate these circumstances.
2. Is it possible to locate the inverse matrix in Excel using VBA?
Ans: Using VBA in Excel, you can locate the inverse matrix. The calculations required to find the inverse matrix can be done by a VBA MInverse function. The function accepts a matrix as an argument, runs the necessary computations, and outputs the inverse matrix.
3. Is the inverse matrix unique?
Ans: The inverse is distinct for an invertible matrix. Every invertible matrix has a single, unique inverse matrix that meets the prerequisites for matrix multiplication to produce the identity matrix.
Things to Remember
- Don’t forget to save the workbook as a .xlsm file after inserting any VBA
- Be careful about the absolute and normal cell references.
- Remember to take the square matrix as input for the Inverse Matrix.
In conclusion, it can be said that the Inverse Matrix in Excel is quite useful for mathematical analysis. From this article, it’s clear that we can determine the inverse in many ways in Excel. Hope, it will come in handy for you. Please feel free to leave a comment if you have any queries or suggestions.