Installing and Using the OLS Regression Add-in and OLSReg Function

Software for
Introductory Econometrics
By
Humberto Barreto and Frank M. Howland
[email protected] and [email protected]
(765) 361-6315 and (765) 361-6317
WARNING:
This software was written and designed for teaching purposes. It has been tested
on several examples but not on a wide variety of data sets. For mission-critical
projects, always check the results with full-fledged statistical programs.
The OLSRegression add-in is OLSRegression.xla (on the CD-ROM, it is in BasicTools\ExcelAddIns\OLSRegression). The OLSReg function is not on the CD-ROM for the first edition of the book, as it
is a new addition as of 25 July 2006.
PURPOSE OF THIS DOCUMENT
This document describes how to install and use the Excel add-in OLSRegression.xla and the
function OLSReg, both of which run OLS regressions. The function will give exactly the same
results as the add-in.
BRIEF DESCRIPTION OF THE ADD-IN
The OLSRegression Add-In is similar to Excels DataAnalysis:Regression tool, but it has several
advantages:

The user can select noncontiguous ranges for the X variables

The user can display the output on the same worksheet on which the data are stored.

The add-in reports robust standard errors.

The add-in can deal with missing values (it drops observations that have missing values
on one or more variables from the regression and reports how many observations were
missing). Any nonnumeric value (including blanks) is treated as missing.

The add-in can handle more than 16 X variables (tested with hundreds).

277522709.doc

Page 1 of 12

The add-in detects perfect and near-perfect multicollinearity in the Xs. Excel 2003 does
if anything a better job than the add-in, but this capacity was not incorporated into
versions prior to Excel 2003.

The OLSReg function has the same advantages over Excels LINEST function. In addition,
OLSReg reports the results in a much more readable fashion than does LINEST because it
includes labels for the statistics and the variables. Like LINEST, the OLSReg function will recompute every time the data changes. This makes OLSReg very useful for Monte Carlo studies
in conjunction with the MCSim Add-In, which is also available from our web site.
INSTALLING AND LOADING
Microsoft offers the following description of an Excel add-in:
Add-ins are programs that add optional commands and features to Microsoft
Excel. Before you can use an add-in, you must install it on your computer and
then load it in Microsoft Excel. Add-ins (*.xla files) are installed by default in the
Library folder in the Microsoft Excel folder. Loading an add-in makes the feature
available in Microsoft Excel and adds any associated commands to the
appropriate menus. [Microsoft Excel Help, add-ins, overview]
Thus, to install an add-in is to have an add-in file (*.xla) in the Library folder of your hard drive.
To load it, you must complete an additional step using the Add-In Manager. Fortunately, you
need to do this only once.
Step 1: Installing the OLSRegression.xla file
If you are accessing the OLSRegression.xla add-in from a CD-ROM, place the CD in your
computer. If accessing the add-in from a network server, make sure you can read from the
appropriate network drive.
If you do not have CD-ROM or network access to the OLSRegression.xla add-in, download it by
visiting <www.wabash.edu/econometrics>. Download the OLSRegression.xla file directly to the
appropriate add-ins folder on your hard drive (usually, C:/Program
Files/MicrosoftOffice/Office/Library) or move it there after downloading to your hard drive.
Step 2: Loading the OLSRegression.xla add-in
Once the OLSRegression.xla file is accessible, launch Excel and use the
Add-In Manager to load the OLS Regression add-in. First, open the
Add-In Manager by clicking on the Tools menu item and selecting AddIns.

277522709.doc

Page 2 of 12

If the OLSRegression add-in is not listed in the Add-Ins scroll box (as in the example above),
click the Browse (or Select) button, navigate to the OLSRegression.xla file on the CD-ROM or
network drive, select it, and click OK.
Mac Note: Some versions of
OfficeX report Unable to Copy
add-in to the Add-ins folder.
This is a bug. The add-in really
is there. Simply quit Excel, then
restart it, return to the Add-In
Manager, and continue following
the instructions.

Click OK if you are asked to write the OLSRegression.xla file to the Addins (or Library) folder.
The Add-In Manager dialog box will now list the OLSRegression add-in.
The Add-In Manager lists all of the installed add-ins, and those with checkmarks are also loaded.
Microsoft offers the following advice, To conserve memory, unload add-ins you do not use
often. Unloading an add-in removes its features and commands from Microsoft Excel, but the
add-in program remains on your computer so you can easily load it again. [Microsoft Excel
Help, add-ins, overview]
Make sure to select the check box next to the OLSRegression add-in and click OK.
Excel will load the OLSRegression.xla file and notify you of successful installation with the
following message:

277522709.doc

Page 3 of 12

USING THE OLSREGRESSION.XLA ADD-IN:

We use the file OLSRegressionAddinExample.xls to demonstrate the workings of the OLS
Regression add-in. Note that this file contains Excel macros, so you should encounter a warning
message asking whether you wish to enable the macros. Those macros are necessary for the
OLS Regression function.
The Data sheet looks like this:

In this example, there are 12 observations on 4 independent variables A, B, C, and D and one
dependent variable, Y. Notice that there are two missing values, the seventh observation on B
and the first observation on Y.
Suppose that you wish to regress Y on variables A, B, and D. To do so, go to Tools: OLS
Regression A user form appears:

277522709.doc

Page 4 of 12

To use the add-in, you must first select the ranges on the worksheet that contain the X and Y
variables, then make some choices about the regression and its output, and finally decide where
to put the output. The boxes containing the information on the X and Y variables are RefEdit
boxes. Click on the minus signs on the right-hand side,
, and the user form will disappear,
allowing you to navigate around the sheet and select the location of the data for the regression.
You can select noncontiguous ranges by holding down the CTRL key. Here is an example:

277522709.doc

Page 5 of 12

Note that we have selected two noncontiguous ranges (areas) for the X variables. The first range,
Data!$B$1:$C$13, contains variables A and B; the second range, Data!$E$1:$E$13, contains
variable D. You can select three or more noncontiguous ranges.
The following rules should be followed: The data must be organized in columns with labels for
each variable in the first row of each column. Each variable must be in a single column. You
must select the cells containing the labels for the variables.
After you have made your X variable selection, click on the minus sign (which now looks like
this:
) to go back to the user form. Make the Y variable selection in the same way.
The next step is to make some choices about the regression model and what output you want to
see:

The default is to include an intercept (constant) term in the regression; you can uncheck this box
if you wish to run a regression without an intercept. If you wish to see the residuals, click on the
Report Residuals box. The residuals will be reported in the area beneath the regression results.
If you have more than 50 regressors (X variables), you can check the more than 50 Xs option.
This will not use Excels MINVERSE function (which is limited to 50 x 50 in Excel 2003 and
earlier and 59 x 59 in Excel 2007) to compute the (XX)-1 matrix. It uses direct matrix
multiplication and the Cholesky decomposition to compute this matrix. It is very slow. Do not
use this option unless you have a special need and be prepared to wait. You must be patient.
Finally there are four different types of robust SE that can be reported. A companion document,
TypesOfRobustSEs.doc, describes these in some detail, using matrix algebra. If you do not
know what a robust SE is, you can just ignore this option and the output generated that refers to
the robust SE until you learn about heteroskedasticity. If you are aware of the use of robust
standard errors, let us simply note that HC0 is the robust standard error described in the textbook
and that the other types are more complicated variants of HC0. Econometric theory suggests that
HC1 is preferable to HC0 for small samples; Monte Carlo evidence shows that HC2 and HC3 are
better still, but which is best is not clear. We have made the default HC2. The only reason you
might use HC0 is to confirm calculations based on the discussion in the book.
The final choice you must make is between putting the output on a separate worksheet and
putting it on the current worksheet. To do the latter, click on the appropriate radio button and
select the cell in which you want the upper-left-hand corner of the output to be located:

277522709.doc

Page 6 of 12

Here is the output we obtained:

The top part of the display gives general regression statistics. For example, cell I3 tells us that
there are 2 missing observations. The abbreviation SSR stands for the sum of squared residuals,
TSS denotes total sum of squares. When there is no intercept term, the TSS is the sum of the
squared values of the Y variables; when there is an intercept, TSS is the sum of the squared
deviations of the dependent variable observations from their mean. The formula for R2 is
R2 1

SSR
.
TSS

The formula for the (whole-model) F-statistic is

TSS SSR
F

SSR

k,

n k

where k is the number of parameters estimated (including the intercept term if it is estimated).
The OLS Regression add-in will detect situations in which the X variables suffer from perfect or
near-perfect multicollinearity and refuse to run the regression, producing a warning message that
looks like this:

277522709.doc

Page 7 of 12

In such situations, the user should try to figure out whether he or she has made a mistake in
setting up the model (e.g., including dummy variables for both males and females). If not, then
you should consider dropping one of the highly correlated X variables.
The OLSReg function:
We turn now to a discussion of the OLSReg function. Like Excels LINEST, the OLSReg
function is an array function. This means that the results are put into an array of several cells
rather than a single cell. Furthermore, rather than hitting Eter once you have typed the formula,
you must simultaneously hit the three-key combination, CTRL-Shift-Enter. The number of rows
in the array is five plus the number of parameters being estimated; the number of columns is
always four. Thus, if you are estimating three parameters, select an array of size 8 rows by 4
columns, type the function into the formula bar, and then hit CTRL-Shift-Enter. The syntax of
the function is as follows:
OLSReg(Xdata,Ydata, Intercept Included, Type of Robust SE)
Xdata must be a range of data of containing the labels in the top row. For example A1:C101
would be a range containing 100 observations on 3 variables with the labels in row 1 and values
of the first independent variable in cells A2 to A101, the values of the second independent
variable in cells B2 to B101, and the values of the third independent variable in cells C2 to C101.
This range can be noncontiguous. We explain this possibility via an example below.
Ydata must be a range of data of containing the label in the top row. For example D1:D101
would be a range containing a label and, beneath it, 100 observations on the dependent variable.
Intercept Included is an optional argument whose default value is TRUE. When Interecept
Included is TRUE, an intercept is include in the regression model; when it is FALSE, an
intercept is omitted. (You may substitute the number 1 for TRUE and the number 0 for FALSE
when writing the function.)
Type of Robust SE is another optional argument whose default value is 2. This argument tells
the function what sort of robust standard error to report in the results. The classification of
robust standard errors is due to Davidson and MacKinnon (1993, page 554). The default is what
Davidson and MacKinnon label HC2. For more information, see either their book or the
document TypesOfRobustSEs.doc, available in the Basic Tools\ExcelAddIns \OLSRegression
folder.

277522709.doc

Page 8 of 12

Once you have hit CTRL-Shift-Enter, the formula will appear in the formula bar surrounded by
curly brackets, as in this example:
{=OLSReg(B2:B22,D2:D22,TRUE,3)}
The curly brackets indicate that you are dealing with an array function. To change the arguments
in the function, select any cell in the array, type in the change, and then hit CTRL-Shift-Enter. If
you merely hit enter, you will encounter a warning message which says that you cannot change
part of an array. If this happens to you, hit the OK button in response. You can then hit the Esc
key to exit the formula and leave it unchanged.
Example:
We once again use the file OLSRegressionAddinExample.xls to demonstrate the workings of the
OLS Regression add-in and the OLSReg function. As a reminder, the Data sheet looks like this:

In this example, there are 12 observations on 4 independent variables A, B, C, and D and one
dependent variable, Y. Notice that there are two missing values, the seventh observation on B
and the first observation on Y.
Suppose you wish to run an OLS Regression of Y on variables A, B, and D. Some preliminary
work is necessary in order to deal with the fact that the data is in noncontiguous columns. The
solution is to name the range we wish to use. First we select cells B1 to C13, then depress the
CTRL key, then select cells E1 to E13, all the while holding down the CTRL key. Next go to the
Name Box in the upper left, immediately above cell A1. We typed in XData and hit the Enter
key. This named the range. The XData range is depicted in the figure below as the shaded
portion of the worksheet|:

277522709.doc

Page 9 of 12

We then selected the range H17 to K25. This range contains 9 rows and 4 columns. We used 9
rows because we are estimating four parameters, an intercept and three slopes and we need five
rows for other output. Next we went to the formula bar and typed in:
=OLSReg(XData,F1:F13)
This tells the function that the data on the independent variables consist of 12 observations on the
three variables A, B, and D contained in the range named Xdata (note that you must include
variable labels to use this function, though those labels could be blanks), that the data on the
dependent variable consist of twelve cells in the range F2 to F13 (cell F1 contains the label Y),
that the regression should include an intercept, and that the type of robust standard error to
include is HC2. The last two choices are by default since we have put in only two arguments in
the function.
Finally, we hit CTRL-Shift-Enter to actually insert the array function into the worksheet.
Limitations
1) The OLSReg function has occasional problems running very slowly with Monte Carlo
simulations. The difficulty appears to be that when the regression is based on data which is
changing as random number generators re-compute values in the data set, OLSReg will attempt
to re-compute the regression as each cell changes. One remedy is to minimize the number of
functions like OLSReg on the worksheet. Also, the function works much quicker after the first
time that all the cells recalculate. Make sure that the cells recalculate to 100% the first time
this may take a while. This message will show up in the bottom of the left-hand-side of the
Excel workbook:
to 100%.

277522709.doc

, where the percentage will start at 0% and gradually increase

Page 10 of 12

2) In addition OLSReg will occasionally report error messages before producing the right result.
The problem seems to be that OLSReg evaluates the data before it recognizes the cells as
actually containing values. This is an example:

3) Like all functions, the OLSReg function will give uninformative results if you make a minor
typographical error in typing the function. #NAME? means that you have miss-typed the name
of the function; #VALUE means that you have made a typographical error in the arguments of
the function.
4) The OLS Regression Add-In uses an unsophisticated algorithm for computing the regression
estimates.1 This means that it can give poor results in some cases. The file NISTTests.xls shows
how the OLS Regression Add-In and the Excel function LINEST perform when confronted with
various challenging data sets. The OLSReg function should give exactly the same results. We
found that the OLS Regression Add-In does a poor job or fails to be able to produce estimates
when the data are highly collinear, but that Excel 2003 did a better, though not perfect, job with
such data sets. We believe that Excel 2003, the OLSReg function and the OLS Regression AddIn are perfectly adequate for most teaching purposes and for exploratory data analysis. Any
work for publication should be done with more sophisticated econometric software.
For more comprehensive results and references pertaining to regression analysis in Excel 2000
and 2003 see "VIII. LINEAR AND POLYNOMIAL REGRESSION" in "MICROSOFT EXCEL
2000 AND 2003 FAULTS, PROBLEMS, WORKAROUNDS AND FIXES" by David Heiser
available from <www.daheiser.info/index.html>.

It uses Excels MINVERSE function to invert the XTX matrix.

277522709.doc

Page 11 of 12

THIS VERSION
The latest OLSRegression.xla version is 14 October 2007.
To check the date of your installed add-in, execute Tools: Add-ins and then highlight the add-in.
The Add-Ins dialog box displays the date at the bottom.

To install this for the first time, please follow the instructions on the first page of this document.
To install over a previous version that is already installed, please see
InstallingAddinOverPreviousVersion.doc
ADDITIONAL HELP AND FEEDBACK:
If something goes wrong in the installation or loading process, an unexpected error keeps
recurring, or you have other problems, please contact us. We are interested in your comments,
suggestions, or criticisms of the OLSRegression.xla software.
www.wabash.edu/econometrics
Humberto Barreto
Wabash College
[email protected]
(765) 3616315

277522709.doc

Frank Howland
Wabash College
[email protected]
(765) 3616317

Page 12 of 12