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.
Figure 1
Equation Set 1
Equation Set 2
Equation Set 3
Equation Set 4
Equation Set 5
Figure 2
