Download MS Excel Exercise and more Summaries MS Microsoft Excel skills in PDF only on Docsity!
Computer Handout 01: Simple Regression in Excel
Diego Escobari Econ 3341
The goal of this Computer Handout 01 is to use MS Excel to implement the following Ordinary Least Squares estimators:
(^) n
i
i
n
i
i i
X X
X X Y Y
b
1
2
1 2 ( )
b 1 (^) Y b 2 X
The file BEAUTY.DES contains a short description of the 17 variables included in the corresponding MS Excel file “BEAUTY.xls”. In particular, in column 1 we have wage and in column 5 we have exper (experience).
To make computations easier, copy columns 1 and 5 (wage and exper) from the file BEAUTY.xls into a separate sheet or to a separate MS Excel file. Then label the two variables y = wage and x = exper.
To implement the formulas above, let’s first calculate the average of x and the average of y. Just go to the end of these two columns and type =average(A2:A1261). Just make sure to select all the corresponding observations to calculate the average.
On column C, let’s calculate the deviations from the mean (Xi-Xbar). Therefore on cell C2 type:
To make sure you lock the formula of the average, make sure you include the dollar signs $ in the formula.
Double click on the bottom-right corner of cell C2. This will automatically copy the formula on C2 to all cells in column C calculating the values of (Xi-Xbar) for all the observations in the sample.
Do the same thing on column D for the deviations from the mean for the variable y: (Y- Ybar).
On column E, get the product of columns C and D. On column F get the square of column C. This can be done by writing the formulas =C2*D2 and =D2^2 on cells E2 and F2 respectively. Then just pull down the formulas by double-clicking on the lower-right corner of cells E2 and F2.
Once you pull down the formulas, you need to calculate Σ(Xi-Xbar)(Yi-Ybar) and Σ(Xi- Xbar)^2 , which are the numerator and the denominator in the formula for b 1 above. In order to do this, type =SUM(E2:E1261) and =SUM(F2:F1261) on cells E1263 and F1263.
Once you have these calculations done, it is easy to get the values for b 0 and b 1 by just implementing the last step of the formulas above. You can do this in one of the cells.
For b 2 : (notice that b 2 is named b1 in the Excel file)
To use the Excel built-in procedure to run a regression you first need to make sure that the “Data Analysis” tool is active. Click the “Office Button” in the upper right corner of the screen. Then go to “Excel Options” then “Add-Ins” then select “Analysis ToolPak” and then click “Go.” A new window will open and you will have to select “Analysis ToolPak” again and click “OK.” This should activate the “Data Analysis” tool. In the newer versions of Excel you need to go to “File” and then “Options.” The rest of the steps are the same as before.
Now, go “Data” in the main menu and on the right hand side you will find the icon “Data Analysis.” Click it and then go to “Regression” and OK. You will have to select the input range for both variables Y and X and then a single cell for the output. Once you are done, you should be getting the following output:
