Simple Linear Regression in Microsoft Excel

Introduction

• Tutorial on performing and analyzing a simple linear regression in Excel.
• Demonstration using data: weight (kg) and height (cm) of 49 participants.
• Aim: Predict weight using height.
• Tools: Analysis ToolPak add-on in Excel.

Setting Up Analysis ToolPak

• Steps to Install:
  1. File → Options → Add-ins.
  2. Manage Excel add-ins → Go.
  3. Tick Analysis ToolPak → OK.
• Data Analysis button appears under Data ribbon, in Analyze section.

Performing Linear Regression

• Steps:
  1. Data Analysis → Regression.
  2. Input Y Range (Dependent Variable): Select Weight column.
  3. Input X Range (Independent Variable): Select Height column.
  4. Tick Labels (if column labels are selected).
  5. Leave Constant is Zero unchecked (common practice).
  6. Confidence Level: Defaults to 95% (can change if needed).
  7. Output Options: Choose output location (new worksheet recommended).
  8. Residuals Options: Check all for comprehensive output.
  9. Click OK.

Outputs from Regression

• Summary Output Table:

  • Multiple R: Correlation coefficient (0.65 suggests strong linear correlation).
  • R Squared: Coefficient of determination, variance explained by the model (43%).
  • Adjusted R Squared: Adjusts R squared for number of predictors (relevant for multiple regression).
  • Standard Error: Average distance of observed values from regression line (4.31 kg).
  • Observations: Number of data points (49).

• ANOVA Table:

  • Significance F (p-value): Tests model significance (p-value < 0.05 means model is significant).
  • Hypotheses: Null (no linear relationship), Alternative (linear relationship exists).

• Coefficients Table:

  • Display results for intercept and slope.
  • Linear Equation: Y = 0.800264X - 79.599
  • Interpretation: Use height to predict weight.

Detailed Outputs Interpretation

• Residual Output Table: Difference between actual and predicted values.

  • Example: Residual for first participant (-2.867 kg).

• Residual Plots: Scatter plot of residuals vs independent variable.

  • Assessing homogeneity of variance (random pattern expected).

• Standardized Residuals: Standardized residuals (z-scores) to identify outliers.

  • Values > |3| indicate potential outliers.

• Line Fit Plots: Scatter plot of actual vs predicted values.

  • Can add/remove trendlines and predicted values.

• Normal Probability Plots: Determines if the Y data fits normal distribution.

  • Straight line indicates normal distribution.

Conclusion

• Complete understanding of performing and interpreting simple linear regression in Excel.
• Exploring additional linear regression assumptions and details in future tutorials.
• Encouragement to like, comment, and subscribe for more content.