Linear Regression Analysis Using MS Excel

1
Linear Regression AnalysisUsing MS Excel

• Tutorial for Assignment 2
• Civ E 342

2
The Regression Analysis Procedure
Import Data to Excel
Correlation Analysis
Assume Equation
Prepare Data Table
Regress Using Excel
Quality Analysis
Yes
Try More Equ.?
No
Select the Best Equ.
3
Step 1 Import Data to Excel

• Objective
• Import original data to Excel. If it is a text
  file, open the file in Excel and follow the
  steps!
• Label each column - variable names
• Define (label) independent and dependent
  variables (x1,x2,...,y)
• Demo

Import Data to Excel
Correlation Analysis
Assume Equation
Prepare Data Table
Regress Using Excel
Quality Analysis
Yes
Try More Equ.?
No
Select the Best Equ.
4
Step 2 Correlation Analysis

• Objectives
• Important factors?
• Correlation between factors?
• Factors with non-linear effect - transform into
  linear if necessary
• Method 1 Correlation matrix
• Excel - Tools - Data Analysis - Correlation
• Identify the factors that are correlated - high
  coefficient of correlation
• Method 2 Scatter diagram
• Demo

Import Data to Excel
Correlation Analysis
Assume Equation
Prepare Data Table
Regress Using Excel
Quality Analysis
Yes
Try More Equ.?
No
Select the Best Equ.
5
Step 3 Assume a Functional Form

• Objective
• Identify factors and form of relationship
• Methods
• A trial-and-error process
• Stepwise process (not covered in this tutorial)

Import Data to Excel
Correlation Analysis
Assume Equation
Prepare Data Table
Regress Using Excel
y ?0 ?1 x1 y ?0 ?1 x1 ?2 x2
Quality Analysis
Yes
Try More Equ.?
No
Select the Best Equ.
6
Step 4 Prepare Data Table
Import Data to Excel

• Objective
• For the assumed equation

Correlation Analysis
y ?0 ?1 x1 ?2 x2
Assume Equation
Prepare Data Table
Regress Using Excel
Quality Analysis
Yes
Try More Equ.?
No
Demo
Select the Best Equ.
7
Step 5 Regress Using Excel

• Objective determine the coefficients that make
  the equation best fit to the data
• Method
• Method of Least Squares
• Demo

Import Data to Excel
Correlation Analysis
Assume Equation
Prepare Data Table
Regress Using Excel
Quality Analysis
Yes
Try More Equ.?
No
Select the Best Equ.
8
Step 6 Assess the Adequacy of the Regression
Model

• Objective
• Measures
• Coefficient of Determination (R2)
• t-tests
• tigtt?, n-k-1, ?-level of significance
• n-sample size
• k-no of independent variables
• Residual plot
• Intuitive acceptability
• Demo

Import Data to Excel
Correlation Analysis
Assume Equation
Prepare Data Table
Regress Using Excel
Quality Analysis
Yes
Try More Equ.?
No
Select the Best Equ.
9
Step 7 Select the Best Regression Model
Import Data to Excel

• Objective
• Model Application
• Models are valid only for the values of the
  variables within the range of the original data!

Correlation Analysis
y ?0 ?1 x1 ?2 x2 t1 t2 R2
Assume Equation
Prepare Data Table
Regress Using Excel
Quality Analysis
Yes
Try More Equ.?
No
Select the Best Equ.