Generalized Inverse using Excel
This section of Regression tutorial is very useful to introduce Generalized Inverse matrix. Reading this section may give you a better preparation for further numerical analysis of curvilinear regression, multiple linear regressions and non-linear regression. You may also learn how you can compute linear regression using matrix formula as you may see in the advance statistical books. At least, after reading this section, you may be motivated to begin to read more advance statistical books. They could be very useful for your research or study. Solving the simple linear regression using matrix by itself, however, might not practical because you can use other methods in MS excel to solve linear regression easily.
You may download the spreadsheet example of this tutorial here
Our model is a linear model, say . If we listed our model for each data, we have
or in matrix form
Suppose we name each of them as matrix , vector , vector and vector , then we can write the equation above in more simple way:
Our objective is to find given matrix and vector . However, matrix is not a square matrix in general. Thus, we cannot find its inverse. To solve this problem we need a generalized inverse. The trick is to multiply matrix with its transpose (either form the left or from the right) and the result of this multiplication will become a square matrix that we can find its inverse. We have
Multiplying the two side of the equation with we get the equivalent
Now we multiply the two side of the equation with we get the equivalent formula of
Since multiplication of a matrix with its inverse produce identity matrix, we have
is called (left) generalized inverse of matrix .
Note that when we multiply , we obtain
In addition, when we multiply , we get
If have read the manual computation you will notice all of those elements are in the formula to compute the slope of regression line.
To do this in Microsoft excel is quite straightforward. We will use the five point data of population as in the manual computation.
1. We type matrix and vector from the data and define name matrix_A and vector_y (using menu Insert > Name-Define ) for them.
2. Next we get the transpose of matrix by MS excel function = TRANSPOSE( matrix_A ) and press Enter. Then highlight the cells where the transpose will be place started from the cell that contains the function. Press F2 and then SHIFT-CTRL-ENTER. You get the transpose matrix and name it matrix_At for
3. We need to obtain using =MMULT(matrix_At, matrix_A). Hightlight 2 by 2 cells to the right and down from the cell that contain the function. Press F2 and then SHIFT-CTRL-ENTER. Name this matrix_AtA .
4. To get the inverse matrix , we use =MINVERSE( matrix_AtA ). Hightlight 2 by 2 cells to the right and down from the cell that contain function. Press F2 and then SHIFT-CTRL-ENTER. Name this inv_At_A.
5. Next, we want to get the generalized inverse matrix . The size of this matrix should be the same as . We obtain it by multiplication of the inverse matrix of step 4 with the transpose matrix from step 2. We use MS excel function =MMULT( inv_At_A , matrix_At ). Hightlight 2 by 5 cells to the right and down from the cell that contain the function. Press F2 and then SHIFT-CTRL-ENTER. Name this matrix_L.
6. Lastly we multiply the generalized inverse with vector to find . Using =MMULT( matrix_L , vector_y ). Hightlight 2 by 5 cells to the right and down from the cell that contain the function. Press F2 and then SHIFT-CTRL-ENTER.
As the result we get
See also: Generalized Inverse
Preferable reference for this tutorial is
Teknomo, Kardi (2015) Regression Model using Microsoft Excel. http://people.revoledu.com/kardi/tutorial/Regression/