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.

Overview to Calculate Inverse Matrix in Excel


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.

Calculate Inverse Matrix of a 2×2 Matrix

  • Just insert the following formula in a new cell.
=MINVERSE(C6:D7)

Resulted Inverse Matrix of a 2×2 Matrix

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.
=MINVERSE(C6:E8)

Calculate Inverse Matrix of a 3×3 Matrix


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.
=MINVERSE(C6:F9)

Calculate Inverse Matrix of a 4×4 Matrix


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()))

Determining Adjoint Matrix

  • Also, in another cell insert the following formula to get Determinant. Let’s use the MDETERM function.
=MDETERM(C6:E8)

Finding Matrix Determinants

  • Afterward, use the following formula in a cell to get the desired inverse matrix. We will simply divide the Adjoint matrix by the Determinant.
=B11#/E11

Inverse Matrix with Formula


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.

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

VBA Code to Make User Defined Function for Inverse Matrix

Code Breakdown:

  • 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.
=inverseMatrixA(C6:E8)

User Defined Function for Inverse Function


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.
=MMULT(C6:E8,C13:E15)

Multiplication of Matrix with Inverse Matrix to Get 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.

Get FREE Advanced Excel Exercises with Solutions!
Mehedi Hasan Shimul
Mehedi Hasan Shimul

Md. Mehedi Hasan, with a BSc in Electrical & Electronic Engineering from Bangladesh University of Engineering and Technology, holds a crucial position as an Excel & VBA Content Developer at ExcelDemy. Driven by a deep passion for research and innovation, he actively immerses himself in Excel. In his role, Mehedi not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently deliver exceptional and quality content.... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo