Multiple Regression Model

This example demostrates how to run multiple regression using matrix algebra by utilizing the Excel functions in a VBA program.
The key Excel functions that are needed for matrix algebra in this example are, MInverse( ) and MMult( ), which perform matrix inversion and matrix multiplication, respectively. Since Excel does not provide a function for matrix transposition, the user has to write the program. It is written in the section 2 of the MultOLS( ) Sub procedure. Another very important Excel function is Index( ). It returns the value of a specified cell in a matrix. Without using this function with MInverse( ) and MMult( ), the computer will return only one value instead of an array.

All the formulas required to perform multiple regression in this example are displayed in Equation set 1 to 5 below. The matrix notations are shown in bold face.
To use this program, the user needs to create the following sheets: "Input Sheet" & "Output Sheet". The user may not need to set up the format as shown in Figures 1and 2. For these sheets, it is very important that the data input are placed in the same cell references as in Figure 1. This program will regress up to 6 independent variables and 100 observations for each variable. The user can easily increase the number of variables (even 50 variables!) and observations by changing the range within the count( ) function on the second and the third line of the Sub procedure, MultOLS( ). Note that, the independent variable 1 is denoted as X2. The reason is that, we want to reserve X1 and B1 for the constant term.