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.

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:

1. Calculate the determinant of A: det(A) = ad - bc
2. Find the adjoint of A: adj(A) = [[d, -b], [-c, a]]
3. 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:

1. Calculate the matrix of cofactors of A, denoted C.
2. 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 a #VALUE! error if:

• the argument contains text or blanks.
• the number of rows and columns don't match.

3.1 Troubleshooting the error value

When you encounter an error value in a cell a warning symbol appears, displayed in the image above. Press with mouse on it to see a pop-up menu that lets you get more information about the error.

1. The first line describes the error if you press with left mouse button on it.
2. The second line opens a pane that explains the error in greater detail.
3. The third line takes you to the "Evaluate Formula" tool, a dialog box appears allowing you to examine the formula in greater detail.
4. This line lets you ignore the error value meaning the warning icon disappears, however, the error is still in the cell.
5. The fifth line lets you edit the formula in the Formula bar.
6. The sixth line opens the Excel settings so you can adjust the Error Checking Options.

Here are a few of the most common Excel errors you may encounter.

#NULL error - This error occurs most often if you by mistake use a space character in a formula where it shouldn't be. Excel interprets a space character as an intersection operator. If the ranges don't intersect an #NULL error is returned. The #NULL! error occurs when a formula attempts to calculate the intersection of two ranges that do not actually intersect. This can happen when the wrong range operator is used in the formula, or when the intersection operator (represented by a space character) is used between two ranges that do not overlap. To fix this error double check that the ranges referenced in the formula that use the intersection operator actually have cells in common.

#SPILL error - The #SPILL! error occurs only in version Excel 365 and is caused by a dynamic array being to large, meaning there are cells below and/or to the right that are not empty. This prevents the dynamic array formula expanding into new empty cells.

#DIV/0 error - This error happens if you try to divide a number by 0 (zero) or a value that equates to zero which is not possible mathematically.

#VALUE error - The #VALUE error occurs when a formula has a value that is of the wrong data type. Such as text where a number is expected or when dates are evaluated as text.

#REF error - The #REF error happens when a cell reference is invalid. This can happen if a cell is deleted that is referenced by a formula.

#NAME error - The #NAME error happens if you misspelled a function or a named range.

#NUM error - The #NUM error shows up when you try to use invalid numeric values in formulas, like square root of a negative number.

#N/A error - The #N/A error happens when a value is not available for a formula or found in a given cell range, for example in the VLOOKUP or MATCH functions.

#GETTING_DATA error - The #GETTING_DATA error shows while external sources are loading, this can indicate a delay in fetching the data or that the external source is unavailable right now.

3.2 The formula returns an unexpected value

To understand why a formula returns an unexpected value we need to examine the calculations steps in detail. Luckily, Excel has a tool that is really handy in these situations. Here is how to troubleshoot a formula:

1. Select the cell containing the formula you want to examine in detail.
2. Go to tab “Formulas” on the ribbon.
3. Press with left mouse button on "Evaluate Formula" button. A dialog box appears.
   The formula appears in a white field inside the dialog box. Underlined expressions are calculations being processed in the next step. The italicized expression is the most recent result. The buttons at the bottom of the dialog box allows you to evaluate the formula in smaller calculations which you control.
4. Press with left mouse button on the "Evaluate" button located at the bottom of the dialog box to process the underlined expression.
5. Repeat pressing the "Evaluate" button until you have seen all calculations step by step. This allows you to examine the formula in greater detail and hopefully find the culprit.
6. Press "Close" button to dismiss the dialog box.

There is also another way to debug formulas using the function key F9. F9 is especially useful if you have a feeling that a specific part of the formula is the issue, this makes it faster than the "Evaluate Formula" tool since you don't need to go through all calculations to find the issue..

1. Enter Edit mode: Double-press with left mouse button on the cell or press F2 to enter Edit mode for the formula.
2. Select part of the formula: Highlight the specific part of the formula you want to evaluate. You can select and evaluate any part of the formula that could work as a standalone formula.
3. Press F9: This will calculate and display the result of just that selected portion.
4. Evaluate step-by-step: You can select and evaluate different parts of the formula to see intermediate results.
5. Check for errors: This allows you to pinpoint which part of a complex formula may be causing an error.

The image above shows cell reference B3 converted to hard-coded value using the F9 key. The MINVERSE function requires numerical values which is not the case in this example. We have found what is wrong with the formula.

Tips!

• View actual values: Selecting a cell reference and pressing F9 will show the actual values in those cells.
• Exit safely: Press Esc to exit Edit mode without changing the formula. Don't press Enter, as that would replace the formula part with the calculated value.
• Full recalculation: Pressing F9 outside of Edit mode will recalculate all formulas in the workbook.

Remember to be careful not to accidentally overwrite parts of your formula when using F9. Always exit with Esc rather than Enter to preserve the original formula. However, if you make a mistake overwriting the formula it is not the end of the world. You can “undo” the action by pressing keyboard shortcut keys CTRL + z or pressing the “Undo” button

3.3 Other errors

Floating-point arithmetic may give inaccurate results in Excel - Article

Floating-point errors are usually very small, often beyond the 15th decimal place, and in most cases don't affect calculations significantly.

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.

Functions in 'Math and trigonometry' category

The MINVERSE function function is one of 62 functions in the 'Math and trigonometry' category.

Excel function categories

Excel categories

Leave a Comment