How to Calculate Inverse Matrix in Excel (3 Ways)

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.

• 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

Code:

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

• 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.

Conclusion

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.