Week six

Running Regression on Excel

Doing regression analysis on Excel is a two-step process. Complete the following:

1. Install the Analysis Tool-Pak on your PC.

2. Run the regression analysis.

The Analysis Tool-Pak is an Excel add-in. The Analysis Tool-Pak is part of Excel, but is not installed in the typical initial installation.

The directions on this handout are based on Excel 2007. If you have a different version of Excel, the steps below will be the same, but their location on the menu may be somewhat different. Consult the “Help” function for specifics about your version of Excel.

1. To install the Analysis Tool-Pak on your PC:

a. Click on the “Office” button (the yellow circle on the upper left corner of the screen), then click the “Excel Options” button at the bottom.

b. Click “Add-Ins” on the left panel.

c. Select “Excel Add-Ins” from the dropdown list at the bottom. Then click “Go.”

d. Select the “Analysis Tool-Pak” check box in the “Add-Ins” dialog box, and then click “OK.”

e. If an alert appears that asks if you want to install the Add-In, click yes.

2. Enter your data and run the regression analysis.

3. Enter the data to be used for your problem. Data must be entered by columns, with the independent variables (x-variables) in adjacent columns.

4. The dependent variable can be entered in ether the left or right column of your spreadsheet. For purposes of running the example in this handout, enter the data below as shown.

Sample Problem for Regression Handout

yx1x2

237

4411

6613

8819

10923

5. Click on “Data” (top menu bar), then “Data Analysis,” then “Regression,” and then “OK.”

6. Place the cursor in the “Input Y Range” box. Highlight the range of cells on the spreadsheet that contain the y-variable.

7. Place the cursor in the “Input X Range” box. Highlight the range of cells on the spreadsheet that contain the x-variables. (Recall that they should be entered in adjacent rows or columns.)

8. Click on the “Output Range” button. Place the cursor in a blank cell on the spreadsheet where no spreadsheet content currently appears (either below or to the right of the cell). (Cell A10 should work for this example.) Click on that cell, and the cell address will appear in the box next to Output Range. This cell represents the upper-left corner of the location where your regression output will appear.

9. Click “OK” on the regression dialog box. The regression output will appear. You may need to widen the column widths on your spreadsheet to get the entire variable label to appear.

Sample Problem for Regression Handout

yx1x2

237

4411

6613

8819

10923

SUMMARY OUTPUT

Regression Statistics

Multiple R0.995642681

R Square0.991304348

Adjusted R Square0.982608696

Standard Error0.417028828

Observations5

ANOVA

dfSSMSFSignificance F

Regression239.6521739119.826086961140.008695652

Residual20.3478260870.173913043

Total440

CoefficientsStandard Errort StatP-valueLower 95%Upper 95%

Intercept-1.3478260870.525799418-2.5633845150.124413-3.610158390.914506216

X Variable 10.6956521740.439108911.5842360690.253996-1.1936809782.584985326

X Variable 20.2173913040.1752664731.2403473460.34062-0.5367194630.971502072

10. To help you locate important results, the following are highlighted in yellow. (These are discussed in the textbook.) They are not highlighted in the default output of Excel.

a. Intercept and variable coefficients

b. Standard error of the intercept and of each coefficient

c. T-statistic of the intercept and of each coefficient

d. The probability of obtaining a t-statistic of this value, given that the null hypothesis (the intercept or variable coefficient) is zero. (The null hypothesis holds.)

e. The 95% confidence interval for the variable coefficient and the intercept.

f. The adjusted R-square value

g. The F-statistic and its significance

_1462771089.xls

Sheet1

Sample Problem for Regression Handout
y	x1	x2
2	3	7
4	4	11
6	6	13
8	8	19
10	9	23
SUMMARY OUTPUT
Regression Statistics
Multiple R	0.9956426808
R Square	0.9913043478
Adjusted R Square	0.9826086957
Standard Error	0.4170288281
Observations	5
ANOVA
	df	SS	MS	F	Significance F
Regression	2	39.652173913	19.8260869565	114	0.0086956522
Residual	2	0.347826087	0.1739130435
Total	4	40
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	-1.347826087	0.5257994184	-2.5633845148	0.1244125568	-3.6101583896	0.9145062157	-3.6101583896	0.9145062157
X Variable 1	0.6956521739	0.4391089104	1.5842360688	0.2539961522	-1.1936809778	2.5849853257	-1.1936809778	2.5849853257
X Variable 2	0.2173913043	0.1752664728	1.2403473459	0.3406195243	-0.5367194632	0.9715020719	-0.5367194632	0.9715020719

Sheet2

Sheet3

_1460506165.xls

Sheet1

Sample Problem for Regression Handout
y	x1	x2
2	3	7
4	4	11
6	6	13
8	8	19
10	9	23
SUMMARY OUTPUT
Regression Statistics
Multiple R	0.9956426808
R Square	0.9913043478
Adjusted R Square	0.9826086957
Standard Error	0.4170288281
Observations	5
ANOVA
	df	SS	MS	F	Significance F
Regression	2	39.652173913	19.8260869565	114	0.0086956522
Residual	2	0.347826087	0.1739130435
Total	4	40
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	-1.347826087	0.5257994184	-2.5633845148	0.1244125568	-3.6101583896	0.9145062157	-3.6101583896	0.9145062157
X Variable 1	0.6956521739	0.4391089104	1.5842360688	0.2539961522	-1.1936809778	2.5849853257	-1.1936809778	2.5849853257
X Variable 2	0.2173913043	0.1752664728	1.2403473459	0.3406195243	-0.5367194632	0.9715020719	-0.5367194632	0.9715020719

Sheet2

Sheet3