📊 Chapter 11: Regression & Correlation – Excel Function Guide

📉 1. Simple Linear Regression

Concept	Excel Function	Purpose
Slope	=SLOPE(y_range, x_range)	Estimate β₁
Intercept	=INTERCEPT(y_range, x_range)	Estimate β₀
R-squared	=RSQ(y_range, x_range)	Model fit
Predicted Y	=FORECAST.LINEAR(x, known_ys, known_xs)	Estimate ŷ
Residual	=actual_y - predicted_y	Deviation from line

🧪 2. Hypothesis Test for Slope

Concept	Excel Function	Purpose
Sample size	=COUNT(x_range)	n
t-value	=(slope - 0) / se	t-test of β₁
P-value	=T.DIST.2T(ABS(t_stat), df)	Significance
Critical t	=T.INV.2T(α, df)	Compare to t

🎯 3. Confidence & Prediction Intervals

• Manually compute standard error of prediction
• CI for mean: ŷ ± t × SE(ŷ)
• PI for new obs: wider interval (includes σ̂²)

📈 4. Residual Plot and Adequacy

• Create scatter plot of residuals vs. x or ŷ
• Use =NORM.S.INV(RANK.AVG()/n) for normal plot

🔗 5. Correlation & Covariance

Concept	Excel Function	Purpose
Correlation (r)	=CORREL(x_range, y_range)	Strength of linear relationship
Covariance	=COVARIANCE.S(x_range, y_range)	Linear co-movement

🔄 6. Variable Transformation

• Log: =LN(x)
• Square root: =SQRT(x)
• Reciprocal: =1/x

Then re-run regression using transformed data.

🧮 7. Logistic Regression Model (Basic)

• Logistic function: =1/(1+EXP(-(b0 + b1*x)))
• Odds: =EXP(b0 + b1*x)
• Log-odds: =LN(odds)
• Note: Full MLE requires Solver

📊 8. Bonus: Excel’s Built-In Tool

• Enable: Data → Data Analysis → Regression
• Outputs slope, intercept, R², SE, t-test, ANOVA, etc.
• Does not support logistic regression.