2
$\begingroup$

I was trying to understand how adjusted $R^2$ in a simple linear regression behaves when there exists multicolinearity. And realized I could not replicate the adjusted $R^2$ provided by excel data analysis pack, when I had multiple same input variables.

I created a data set like below:

data

Excel returns R Square and ANOVA table as below: summary

I could replicate the $R^2$ number

$$1 - \frac{SS_{res}}{SS_{tot}}.$$

However, for adjusted $R^2$, my calculation

$$1 - \frac{SS_{res}/(n-k-1)}{SS_{tot}/(n-1)},$$

where $n$ is number of observations and $k$ is number of variables, not including intercept) yields $1 - \frac{1.983/(9-3-1)}{60/(9-1)} = 0.9471$, which is very different from the excel output (0.6765).

I think I might be using the wrong degrees of freedom here but couldn't figure out what's the exact problem.

Improve this question
$\endgroup$

1 Answer 1

Reset to default
1
$\begingroup$

That Excel Adjusted R-squared (0.6765) is wrong and is a result of Excel not being able to handle this situation of perfect multicollinearity. If you look at the coefficients table, you'll see incorrect t-stats for two of the $X$ variables that were dropped from the model.

The correct Adjusted R-squared is 0.962222 and differs from your result because $k$ should be equal to the number of remaining regressors in the model. So rather than $k$ being equal to 3, it should be 1 in your formula.

This result is confirmed by running the same regression in R.

Improve this answer
$\endgroup$
2
  • $\begingroup$ Hi Alex, thanks so much for your reply. In the coefficient table it indeed fails to estimate p-value for the pair of the perfect collinear variables. It seems in order to decide correct # of k to calculate adjusted r2, I should run a correlation first and remove one of perfect correlated variable pairs, the left number of variables is the correct one to use. Is this the correct way to do it or probably a different method is used in R? $\endgroup$
    – Ritaotao
    May 14, 2019 at 16:25
  • $\begingroup$ @Ritaotao, regression software typically automatically removes one (or more) of those variables or refuses to run the regression and returns an explanatory message. If variables were removed, you can just see how many were removed. In this case with Excel, two variables were not actually removed, as Excel seems to not have a clean way to detect and deal with perfect multicollinearity, but the 0 values across the coefficients table and undetermined $p$-values indicate that those variables were perfectly collinear with the remaining variable. $\endgroup$
    – AlexK
    May 14, 2019 at 19:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.