04-11: Correlation & Regression Analyzer

Home  |  All Projects  |  Notes  |  Exercises  |  Quiz Hub

Take a house-price data set from scatterplot to fitted model: correlation matrix, simple regression, residual diagnostics, prediction intervals, then multiple regression with a categorical predictor and a multicollinearity check.

Chapters applied: 13-01 · 13-02 · 13-03

Difficulty: ⭐⭐⭐ Advanced

---

The Data

data/houses.csv — 120 house sales.

Column	Type	Notes
house_id	Nominal	Key
sqft	Ratio	Floor area — the main predictor
bedrooms	Ratio (discrete)	Correlated with sqft — the multicollinearity exercise
age_years	Ratio	Age of the property
garage	Nominal	Yes / No — the dummy-variable exercise
price	Ratio	The response

---

What You Produce

1. A scatterplot matrix and a correlation matrix with p-values
2. A simple regression of price on sqft: slope, intercept, r², s_e, all interpreted in units
3. Inference on the slope: t, p-value, and a 95% confidence interval for β₁
4. Residual diagnostics against all four LINE assumptions
5. Confidence vs. prediction intervals at a chosen sqft, with the width difference explained
6. A multiple regression adding bedrooms, age_years, and a garage dummy
7. Model comparison by adjusted R² and a nested F-test, plus VIF for multicollinearity

---

Excel Route

Step 1 — Look before you fit

Select sqft and price ▸ Insert ▸ Scatter (Markers only). Then right-click a point ▸ Add Trendline ▸ Linear, and tick Display Equation on chart and Display R-squared value.

Never skip this. Anscombe's quartet exists precisely because summary statistics can hide the shape.

Step 2 — Correlation matrix

Data ▸ Data Analysis ▸ Correlation, Input Range = the numeric block (sqft, bedrooms, age_years, price), Grouped By: Columns, tick Labels.

Excel gives r but no p-values, so add them:

=CORREL(B2:B121, F2:F121)                                     ' r
=H2*SQRT((COUNT(B2:B121)-2)/(1-H2^2))                         ' t
=T.DIST.2T(ABS(H3), COUNT(B2:B121)-2)                         ' p-value

Apply Home ▸ Conditional Formatting ▸ Color Scales to the matrix so the pattern is visible at a glance. Watch for high correlations between predictors — those forecast the multicollinearity in Step 7.

Step 3 — Simple regression

=SLOPE(F2:F121, B2:B121)          ' b1  -- note: y FIRST, then x
=INTERCEPT(F2:F121, B2:B121)      ' b0
=RSQ(F2:F121, B2:B121)            ' r-squared
=STEYX(F2:F121, B2:B121)          ' s_e -- the typical prediction error, in dollars
=CORREL(B2:B121, F2:F121)         ' r

' the whole model in one array formula
=LINEST(F2:F121, B2:B121, TRUE, TRUE)
'   row 1:  b1        b0
'   row 2:  SE(b1)    SE(b0)
'   row 3:  r^2       s_e
'   row 4:  F         df_residual
'   row 5:  SSR       SSE
' REMEMBER: LINEST returns the coefficients RIGHT TO LEFT.

Step 4 — Inference on the slope

=INDEX(LINEST(F2:F121,B2:B121,TRUE,TRUE),1,1)          ' b1
=INDEX(LINEST(F2:F121,B2:B121,TRUE,TRUE),2,1)          ' SE(b1)
=$J$1/$J$2                                              ' t = b1/SE(b1)
=T.DIST.2T(ABS($J$3), COUNT(B2:B121)-2)                 ' p-value
=$J$1-T.INV.2T(0.05,COUNT(B2:B121)-2)*$J$2              ' CI lower for beta1
=$J$1+T.INV.2T(0.05,COUNT(B2:B121)-2)*$J$2              ' CI upper

The interval is the useful output: "each extra square foot is worth between $X and $Y" beats "p < 0.001" for any decision-maker.

Step 5 — Residual diagnostics

Data ▸ Data Analysis ▸ Regression, Input Y = price, Input X = sqft, tick Residuals, Standardized Residuals, Residual Plots, Line Fit Plots, Normal Probability Plots.

Read the residual plot against the four LINE assumptions:

What you see	What it means	What to do
Random scatter around 0	The model is fine	Proceed
A U shape	Linearity violated	Add a quadratic term, or transform
A widening funnel	Equal variance violated	Log-transform y, or use robust SEs
One far-out point	Outlier / high leverage	Investigate; report with and without
A wave pattern	Independence violated	Add a time term

Step 6 — Confidence vs. prediction interval

' at x0 = 2000 sqft
=INTERCEPT(F2:F121,B2:B121)+SLOPE(F2:F121,B2:B121)*2000     ' y-hat
=DEVSQ(B2:B121)                                              ' Sxx
=T.INV.2T(0.05, COUNT(B2:B121)-2)                            ' t critical

' CONFIDENCE interval for the MEAN price of all 2000 sqft houses
=$L$1 - $L$3*STEYX(F2:F121,B2:B121)*SQRT(1/COUNT(B2:B121)+(2000-AVERAGE(B2:B121))^2/$L$2)
=$L$1 + $L$3*STEYX(F2:F121,B2:B121)*SQRT(1/COUNT(B2:B121)+(2000-AVERAGE(B2:B121))^2/$L$2)

' PREDICTION interval for ONE new 2000 sqft house  -- note the extra 1 +
=$L$1 - $L$3*STEYX(F2:F121,B2:B121)*SQRT(1+1/COUNT(B2:B121)+(2000-AVERAGE(B2:B121))^2/$L$2)
=$L$1 + $L$3*STEYX(F2:F121,B2:B121)*SQRT(1+1/COUNT(B2:B121)+(2000-AVERAGE(B2:B121))^2/$L$2)

The prediction interval is far wider. Quote it whenever the question is about one house.

Step 7 — Multiple regression

Create the dummy first — the predictor columns must be contiguous for the ToolPak:

=IF(E2="Yes",1,0)                       ' G2: garage dummy, fill down

Then Data ▸ Data Analysis ▸ Regression with Input X = sqft, bedrooms, age_years, garage_dummy.

' Adjusted R-squared, if you want it by hand
=1-(1-R2)*(n-1)/(n-k-1)

' VIF for each predictor: regress it on the OTHERS
=1/(1-RSQ(bedrooms_range, sqft_range))  ' quick 2-variable version
' VIF < 5 fine   5-10 investigate   > 10 serious

The multicollinearity signature: a highly significant overall F with no individual predictor significant. If you see it, check the VIFs before concluding that a predictor "doesn't matter".

---

R Route

Rscript r/analysis.R

Runs every step with full diagnostics and writes regression_plots.png. See r/analysis.R.

Python Route

python python/analysis.py

Same via statsmodels. See python/analysis.py.

---

Checkpoints

• The scatterplot was drawn before any statistic was computed
• Excel's SLOPE/INTERCEPT were called with y first
• The slope is interpreted in units, and the intercept is checked for whether x = 0 is meaningful
• r² is reported as the percentage explained; r is not
• A residual plot is produced and read against all four LINE assumptions
• Confidence and prediction intervals are distinguished
• The categorical predictor enters as k − 1 dummies
• VIFs are checked before dropping any non-significant predictor

---

Extend It

• Add an interaction sqft × garage: do garages add more value to bigger houses?
• Fit log(price) ~ log(sqft) — the slope is then an elasticity (percent change per percent change)
• Compute Cook's distance and identify the most influential sales; refit without them
• Split the data 80/20, fit on the training set, and report the out-of-sample prediction error