by Kardi Teknomo


< Previous | Next | Content >

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 Generalized Inverse Using MS Excel . If we listed our model for each data, we have

Generalized Inverse Using MS Excel

or in matrix form

Generalized Inverse Using MS Excel

Suppose we name each of them as matrix Generalized Inverse Using MS Excel , vector Generalized Inverse Using MS Excel , vector Generalized Inverse Using MS Excel and vector Generalized Inverse Using MS Excel , then we can write the equation above in more simple way:

Generalized Inverse Using MS Excel

Our objective is to find Generalized Inverse Using MS Excel given matrix Generalized Inverse Using MS Excel and vector Generalized Inverse Using MS Excel . However, matrix Generalized Inverse Using MS Excel 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 Generalized Inverse Using MS Excel 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

Generalized Inverse Using MS Excel

Multiplying the two side of the equation with Generalized Inverse Using MS Excel we get the equivalent

Generalized Inverse Using MS Excel

Now we multiply the two side of the equation with Generalized Inverse Using MS Excel we get the equivalent formula of

Generalized Inverse Using MS Excel

Since multiplication of a matrix with its inverse produce identity matrix, we have

Generalized Inverse Using MS Excel

Matrix

Generalized Inverse Using MS Excel

is called (left) generalized inverse of matrix Generalized Inverse Using MS Excel .

Note that when we multiply Generalized Inverse Using MS Excel , we obtain

Generalized Inverse Using MS Excel

In addition, when we multiply Generalized Inverse Using MS Excel , we get

Generalized Inverse Using MS Excel

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 Generalized Inverse Using MS Excel and vector Generalized Inverse Using MS Excel 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 Generalized Inverse Using MS Excel 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 Generalized Inverse Using MS Excel

Generalized Inverse Using MS Excel

3. We need to obtain Generalized Inverse Using MS Excel 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 .

Generalized Inverse Using MS Excel

4. To get the inverse matrix Generalized Inverse Using MS Excel , 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.

Generalized Inverse Using MS Excel

5. Next, we want to get the generalized inverse matrix Generalized Inverse Using MS Excel . The size of this matrix should be the same as Generalized Inverse Using MS Excel . 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.

Generalized Inverse Using MS Excel

6. Lastly we multiply the generalized inverse with vector Generalized Inverse Using MS Excel to find Generalized Inverse Using MS Excel . 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.

Generalized Inverse Using MS Excel

As the result we get Generalized Inverse Using MS Excel

< Previous | Next | Content >

See also: Generalized Inverse

Send your comments, questions and suggestions

Preferable reference for this tutorial is

Teknomo, Kardi (2015) Regression Model using Microsoft Excel. http://people.revoledu.com/kardi/tutorial/Regression/

This tutorial is copyrighted.