Linear Regression Analysis Using MS Excel Tutorial for

Linear Regression Analysis Using MS Excel Tutorial for Assignment 2 Civ E 342

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

Step 1: Import Data to Excel • Objective: Correlation Analysis • 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 (x 1, x 2, . . . , y) Assume Equation Prepare Data Table Regress Using Excel Quality Analysis Try More Equ. ? Yes No Select the Best Equ. • Demo

Step 2: Correlation Analysis • Objectives: Import Data to Excel – Important factors? – Correlation between factors? – Factors with non-linear effect transform into linear if necessary Correlation Analysis Assume Equation • Method 1: Correlation matrix Prepare Data Table – Excel - “Tools - Data Analysis… Correlation” – Identify the factors that are correlated - high coefficient of correlation Regress Using Excel Quality Analysis Try More Equ. ? No Select the Best Equ. • Method 2: Scatter diagram Yes • Demo

Step 3: Assume a Functional Form • Objective Import Data to Excel – Identify factors and form of relationship Correlation Analysis Assume Equation • Methods: Prepare Data Table – A trial-and-error process: Regress Using Excel y = 0 + 1 x 1 Quality Analysis Try More Equ. ? No Select the Best Equ. y = 0 + 1 x 1 + 2 x 2 Yes – Stepwise process (not covered in this tutorial)

Step 4: Prepare Data Table Import Data to Excel • Objective – For the assumed equation: Correlation Analysis y = 0 + 1 x 1 + 2 x 2 Assume Equation Prepare Data Table Regress Using Excel Quality Analysis Try More Equ. ? No Select the Best Equ. Yes Demo

Step 5: Regress Using Excel • Objective: determine the Import Data to Excel coefficients that make the equation best fit to the data Correlation Analysis Assume Equation • Method: Prepare Data Table – Method of Least Squares Regress Using Excel Quality Analysis Try More Equ. ? No Select the Best Equ. Yes • Demo

Step 6: Assess the Adequacy of the Regression Model • Objective: • Measures: Import Data to Excel Correlation Analysis – Coefficient of Determination (R 2) Assume Equation – t-tests ti>t , n-k-1, -level of significance Prepare Data Table Regress Using Excel n-sample size k-no of independent variables – Residual plot Quality Analysis Try More Equ. ? No Select the Best Equ. Yes – Intuitive acceptability • Demo

Step 7: Select the Best Regression Model Import Data to Excel • Objective: Correlation Analysis y = 0 + 1 x 1 + 2 x 2 Assume Equation t 1 Prepare Data Table t 2 Regress Using Excel • Model Application Quality Analysis Try More Equ. ? No Select the Best Equ. Yes – Models are valid only for the values of the variables within the range of the original data!