## 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

Matrix

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

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/