1
Simple Regression
Part I. Setup
A. Be sure you’ve got the data analysis tool pack added in excel
Launch Excel. Under “tools” in the main tool bar at the top of the screen see if the option “data analysis”
is available (it should be at the bottom of the list). If data analysis is not there, do the following:
1. Under “tools” select “add-ins”
2. Click on the box for “analysis toolpak”
3. Click “OK”
4. Go back and check to see that “data analysis” is now an option under tools.
B. Data
For this lab, we’re going to use a data set called “baseball attendance and 12 factors”. It is on my webpage
for ECN 422.
1. Open the data set by clicking the appropriate link on the webpage, then save it as an excel worksheet
(onto the C drive or onto a floppy disk). Be sure to close the browser version of excel and re-open
the newly saved file in excel.
2. Notice at the bottom of the sheet there are three tabs named “variable names”, “Data”, and “sheet3”.
Click on these tabs to move around the file. We’ll mostly use the data sheet, so click on that one. You
should see a data set consisting of 81 observations on 8 variables.
3. To insert a new worksheet into this file, at the main toolbar click “Insert”, then “Worksheet”. A blank
sheet titled “Sheet 1” should now appear to the left of the sheet that was open when you performed
the insert. (Note: to move it to the right of the data sheet, simply click on the tab, then drag it to the
right of the data tab and release). We now have a blank worksheet to do work in without messing up
the original files.
4. Rename this new sheet by double clicking on the “sheet 1” tab and typing a new name (we’re going
to be doing some graphing first, so you can call it “graphing” or whatever).
C. Purpose of Today’s lab
In today’s lab we’re going to learn how to get Excel to perform simple regression.
D. Set up for Today’s Lab
1. Insert 5 new worksheets and re-save the file as an Excel workbook.
2. Label the new sheets “att&pop”, “att&capacity”, “att&priorwin”, “att&curntwin”, and “att&teams”,.
Part II. Simple Regression
Here we’re going to estimate 5 linear relationships: these are the relationships between ATTENDANCE
(Y) and POP, CAPACITY, PRIORWIN, CURNTWIN, and TEAMS (each of these will serve as X).
1. Copy the ATTENDANCE variable into all the new sheets.
2. Copy POP into the “att&pop” sheet and CAPACITY into the “att&capacity” sheet etc.. so that you
have ATTENDANCE and the other variable in each sheet.
3. In the “att&pop” sheet, choose “Tools”, “data analysis”, and “regression”.
4. In the “Input Y range” cell, highlight the ATTENDANCE column, including the top cell as a label.
5. In the “Input X range” cell, highlight the POP column, including the top cell as a label.
Docsity.com
