What is MINVERSE function in Google Sheets?
The MINVERSE function in Google Sheets calculates the multiplicative inverse of a square matrix specified as an array or range. The inverse of a matrix means the reciprocal of a square matrix whose determinant is not equal to zero. In other words, the inverse of a matrix is a matrix that yields the identity matrix when multiplied by the original matrix. A square matrix has an equal number of rows and columns. With the MINVERSE function, we can find the inverse of a matrix containing the same number of rows and columns.
In the example below, we see a 2*2 matrix. To find the inverse matrix of the same, enter the formula =MINVERSE(B2:C3). Press Enter to get the result.
Key Takeaways
- MINVERSE in Google Sheets is used to calculate the inverse of a square matrix. The inverse of a matrix is one which when multiplied by the original matrix, gives us the identity matrix.
- We use the MINVERSE function for solving systems of linear equations. They are also used to solve mathematical equations where several different variables are used.
- The MINVERSE in Google Sheets formula is =MINVERSE(square_matrix), where the square_matrix is a range with an equal number of rows and columns.
- Some square matrices cannot be inverted. They return the #NUM! error as their determinant is 0.
- They can be used with other functions like MMULT to perform complex calculations such as for regressive analysis.
Syntax
The MINVERSE in Google Sheets is useful when calculating the inverse of a matrix for various mathematical operations. The syntax is as follows:
=MINVERSE(square_matrix)
Here,
- square_matrix: The square matrix, which is the range for which you want to find the multiplicative inverse.
How to Use MINVERSE Function in Google Sheets?
We can calculate the inverse matrix in Google Sheets in two ways.
- Entering MINVERSE in Google Sheets manually
- Access from the Google Menu bar
Entering MINVERSE in Google Sheets manually
Let us look at how we can manually enter the function MINVERSE to determine the inverse matrix in Google Sheets.
Step 1: First, let us enter the matrix whose inverse we plan to find in Google Sheets. Here, we enter a 3*3 matrix in the cells A1 to C3.
Step 2: Next, to find the inverse of a 3*3 matrix, select the cell where you want to enter the formula. Now, type =MINVERSE( in the formula bar. Select the matrix area from A1 to C3.
Step 3: Press Enter after closing the braces. Thus, you get the inverse of the matrix in E1:G3 by manually entering MINVERSE.
Access from the Google Menu Bar
Choose any cell and go to the “Insert” tab. Here, go to Function and click on the “Array” drop-down arrow. Scroll and choose the MINVERSE function.
Examples
The MINVERSE function in Google Sheets is used to calculate the inverse of a given square matrix. Let us see how to find the inverse matrix with some examples in Google Sheets.
Example #1 – Solving Systems of Linear Equations
Let us solve systems of linear equations using the MINVERSE function in Google Sheets using matrix algebra principles. Consider the following equations. We will solve for unknown variables x, y and z.
x+y+z =6
2x−y+3z = 14
3x+2y−z=7
Step 1: First, let us calculate the inverse of the array we have entered in Google Sheets using MINVERSE.
Go to cell E8 and enter the following formula to calculate the inverse of A.
=MINVERSE(B1:D3). Press Enter.
Step 2: Calculate the solution vector by multiplying A-1and b
We use MMULT(Range A, Range B) to calculate the solution vector X. Apply the following formula in cell E13.
=MMULT(E8:E10, G1:G3). Press Enter.
You find the solution as shown below.
Example #2
Let us look at using the inverse matrix solution in a real-life example. A group of men and women go to a museum. We have tickets priced at $5 for women and $ 8 for men. The total amount spent by the group is $285. Afterward, they went to a park where the tickets were priced at $3 and $6 for women and men, respectively. They spent a total of $195. Find the number of women and men here.
Step 1: As the first step, we build two matrices, as shown below.
Step 2: The resultant matrix is written as C. It is as shown below.
Step 3: The above matrices are of the form AX = C. Here, we must solve for X. So, multiply the equation by A-1. So, to get the number of women and men, we solve,
X = CA-1.
To find the inverse of A, go to the formula bar, type =MINVERSE(.
Select the matrix A array from D2 to E3.
=MINVERSE(D2:E3). Press Enter.
Step 4: Using the MMULT Google Sheets function, multiply matrix c with the inverse of A. We will get a 1*2 matrix. Type =MMULT(. Select the matrix C from G2 to H2 and the A-1 array from D5 to E6.
The function is: =MMULT(G2:H2,D5:E6).
Step 5: Press Enter. Thus, you get the answers in the resultant matrix, which indicates the number of women as 25 and the number of men as 20.
Example 3 – Regression Analysis (Multiple Linear Regression)
In Multiple Linear Regression, we try to predict a dependent variable Y from several independent variables. In this example, let us look at a case of a perfect regression where when the data values of x increase then the values of y also increase linearly.
Step 1: Consider the following matrix A as shown below. First, to find the perfect regression, calculate the matrix inverse A(inv) of matrix A.
Use the following formula =MINVERSE(B1:D3). Press Enter.
Step 2: The matrix b=A(inv)c=A-1c is found using the matrix product function called MMULT. Apply the following function in cell B10.
=MMULT(B5:D7,F1:F3). Press Enter.
Important Things to Note
- Always ensure that the matrix used is square, else you get an error.
- Remember that sometimes rounding errors result in an inverse matrix that is not exact.
- You can verify the result of your inverse matrix by multiplying the original matrix with the inverse which will result in an identity matrix if it is accurate.
- These inverse matrices are important in engineering, physics, and economics, where matrices are commonly used.
- Using the MINVERSE and MMULT functions, we can easily solve a system of linear equations by performing matrix inversion.
- In engineering, matrix inversion solves system responses in state-space models.
These are some of the main uses of MINVERSE.
Frequently Asked Questions (FAQs)
1. #VALUE! Error: You get the #VALUE error if there are non-numerical data in your matrix. Always ensure that the matrix has entries that are numbers.
2. #NUM! Error: This error indicates that the matrix doesn’t have an inverse. This is usually the case when you use a singular matrix. A singular matrix is one where two rows are identical, or one row is a multiple of another.
3. #REF! Error: We get this error when the range specified in the MINVERSE function is incorrect. Make sure your range selection matches the size of your matrix.
The MINVERSE function has many practical applications.
1. One of the most important applications of MINVERSE is to solve systems of linear equations. It can be done using the matrix inversion method.
2. It is also used in the calculations for multiple linear regression to find the regression coefficients.
3. In finance, MINVERSE minimizes risks and maximizes returns in portfolio optimization.
4. In economics, we use it for regression analysis, least squares estimation, etc.
The main requirements for the inverse matrix are as follows:
1. It should be a square matrix. A square matrix contains an equal number of rows and columns. A non-square matrix, like a 4×5 matrix, cannot be inverted.
2. It should not be a singular matrix where the determinant is zero. In other words, the matrix must be non-singular, which means it must have a non-zero determinant. A singular matrix has no inverse because its rows or columns are linearly dependent.
3. The matrix data must contain numeric values.
Download Template
This article must help understand MINVERSE in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is MINVERSE in Google Sheets. We learn its syntax & how to use it to find the inverse of a square matrix with examples. You can learn more from the following articles. –
Leave a Reply