How to use the MINVERSE function
What is the MINVERSE function?
The MINVERSE function calculates the inverse matrix for a given array.
What is the MINVERSE function useful for?
This function is useful for solving equations with multiple variables.
Table of Contents
1. Introduction
What is a matrix?
A matrix is a rectangular array of numbers, symbols, or expressions arranged in rows and columns. It has a fixed number of rows and columns, elements are accessed by their row and column index,
Represented by capital letters like A, B, C
Basic format is:
A = [[a,b,c],
[d,e,f],
[g,h,i]]
Rows are separated by commas, columns by brackets.
What is the inverse matrix?
For a matrix A, its inverse matrix is denoted A-1. For A to have an inverse it must be a square matrix and have non-zero determinants.
Matrix inversion is a fundamental linear algebra operation. The inverse matrix has applications in solving matrix equations, finding bases, and transforming coordinates.
What is the relationship between a matrix and its inverse matrix?
Matrix = A
Inverse matrix = A-1
A * A-1 = I
I = identity matrix
A matrix with three dimensions has the following identity matrix.
How to calculate the inverse of a 2x2 matrix?
A = [[a, b], [c, d]]
To find the inverse A-1:
- Calculate the determinant of A: det(A) = ad - bc
- Find the adjoint of A: adj(A) = [[d, -b], [-c, a]]
- Compute the inverse: A-1 = 1/det(A) * adj(A)
For example,
B = [[1, 2], [3, 4]]
det(B) = 14 - 23 = 4-6 = -2
adj(B) = [[4, -2], [-3, 1]]
B-1 = 1/-2 * adj(B) = [[-2, 1], [3/2, -1/2]]
So the inverse of matrix B is:
B-1 = [[-2, 1], [3/2, -1/2]]
What is an adjoint?
The adjoint of a matrix, also called the adjugate matrix, and is useful for finding the inverse of a square matrix.
To get the adjoint of an n x n matrix A:
- Calculate the matrix of cofactors of A, denoted C.
- Take the transpose of C to obtain the adjoint matrix, denoted adj(A).
The cofactor matrix C is obtained by replacing each element of A with its cofactor, which involves cross products of matrix minors.
Given the matrix:
A = [[a, b], [c, d]]
The cofactor matrix is:
C = [[d, -b], [-c, a]]
To get the adjoint, we take the transpose of C:
adj(A) = C^T = [[d, -c], [-b, a]]
For example:
B = [[1, 2], [3, 4]]
Cofactor matrix:
C = [[4, -2], [-3, 1]]
Adjoint:
adj(B) = [[4, -3], [-2, 1]]
What is an identity matrix?
An identity matrix is a square matrix with 1s on the main diagonal and 0s elsewhere. It is often denoted by the capital letter I.
For example, a 2x2 identity matrix:
I = [[1, 0],
[0, 1]]
Identity matrices are fundamental to linear algebra and matrix operations. Excel has a dedicated function named MUNIT function that creates an identity matrix of a given dimension.
What is a determinant?
The determinant is a special scalar value computed from a square matrix that provides crucial information about matrix properties and transformations.
Denoted as det(A) or |A| for a matrix A.
Given the matrix:
A = [[a, b], [c, d]]
The determinant is calculated as:
det(A) = ad - bc
For example:
B = [[3, 2], [1, 4]]
det(B) = (3)(4) - (2)(1) = 12 - 2 = 10
2. Syntax
MINVERSE(array)
| array | Required. An array containing numbers, the number of rows and columns must be the same. |
3. Function not working
The MINVERSE function returns #VALUE if:
- the argument contains text or blanks.
- the number of rows and columns don't match.
4. Example 1
The image above shows a 2x2 matrix in cell range B3:C4. The formula in cell B7 calculates the inverse matrix of B3:C4.
Array formula in cell B7:C8:
The MINVERSE function must be entered as an array formula. Seee section 5.1 below on how to enter an array formula in earlier Excel versions.
Cell range B8:C9 and B11:C12 in the image above shows how the MINVERSE function calculates the inverse of a 2x2 matrix. The formulas below describe the exact same calculations.
A = [[a, b], [c, d]]
A = [[1, 2], [3, 4]]
A-1 = [[e, f], [g, h]]
e = d/(a*d-b*c) = 4/(1*4-2*3) = 4/-2 = -2
f = b/(b*c-a*d) = 2/(2*3-1*4) = 2/2 = 1
g = c/(b*c-a*d) = 3/(2*3-1*4) = 3/2 = 1.5
h = a/(a*d-b*c) = 1/(1*4-2*3) = 1/-2 = -0.5
A-1 = [[-2, 1], [1.5, -0.5]]
Our calculations match the output values of the MINVERSE function. We can now check that the matrix and the inverse matrix multiplied returns the identity matrix.
The image above shows that the result of A * A-1 is in fact the identity matrix.
Formula in cell B11:
The MMULT function calculates the matrix product of two arrays, in this example B3:C4 and B7:C8.
4.1 How to enter an array formula
Skip these steps if you are an Excel 365 user.
To enter an array formula, type the formula in a cell range then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
5. Example 2
A triangle's largest angle is twice as large as the next largest. The next largest angle is 14 degrees greater than the smallest. Write the values of the angles in order from largest to smallest?
This question can be solved using linear simultaneous equations and the MMULT and MINVERSE functions. The sum of all angles in a triangle is always 180 degrees.
We can derive the following equations from question above and the fact the the total of all angles in a triangle equals 180 degrees.
A = 2B
B = C + 14
A + B + C = 180
There are three different variables and three different equations. We now need to move the variables to the left side of the equal sign and the numbers on the right side.
A - 2B = 0
B - C = 14
A + B + C = 180
Lets populate the table in cell range C13:F15 based on the equations above, see the image below.
The following array formula calculates the angles in the triangle A, B, and C based on the equations described above.
Array formula in cell range C17:C19:
Excel 365 subscribers may enter this formula as a regular formula, the values will spill as far as needed below cell C17 automatically. See section 5.1 for instructions on how to enter an array formula in earlier Excel versions.
The MINVERSE function calculates the inverse matrix of C13:E15, see the image in section 5 above for how to calculate them in greater detail.
The MMULT function calculates the matrix product of two arrays. The first array is the result of the inverse matrix of C13:E15 and the second array is in F13:F15.
The largest angle is 97 degrees, the next largest angle is 48.5 degrees. The smallest angle is 34.5 degrees. The largest angle is twice as large as the next largest angle. 48.5 * 2 = 97. The next largest angle is 14 degrees greater than the smallest. 34.5 + 14 = 48.5 degrees.
'MINVERSE' function examples
The following article has a formula that contains the MINVERSE function.
This article demonstrates how to solve simultaneous linear equations using formulas and Solver. The variables have the same value in […]
Functions in 'Math and trigonometry' category
The MINVERSE function function is one of 61 functions in the 'Math and trigonometry' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form