adjusted R squared for multiple exact same input variables

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:

Excel returns R Square and ANOVA table as below:

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.

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.
• @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. – AlexK May 14 '19 at 19:57