My starting point is a covariance matrix (Not raw data) and I want to calculate the regression weights. I may have as many as 6-8 independent variables and 1 dependent variable so looking for a matrix solution. Been 20 years since I have done matrix algebra so any instructions for how to do this in Excel are appreciated.
Asked
Active
Viewed 699 times
1
-
@whuber I realize this is a very old thread, but this is not a duplicate of the above question. The asker wants "weights". They want to account for spatial autocorrelation or something similar and already have a variance-covariance matrix, generated from spatial coordinates. – colin Jan 18 '19 at 01:12
-
@colin Your interpretation is inconsistent with the reply posted by the original questioner below. – whuber Jan 18 '19 at 14:48
1 Answers
0
Finally stumbled on the answer. For anyone else interested. You start with a covariance matrix (or correlation matrix if you want standardized regression weights), and you use the minverse matrix formula of the X1 to Xi portion {=MINVERSE(F44:H46)} (look on youtube for how to use matrix formulas). Then multiply this inversed $X^{-1}$ matrix by the $Y$ matrix from the covariance table {=MMULT(F49:H51,E44:E46)}. That's it. Will generate the $b$ weights that you need. And from there its simple equations to get you to the $F$ and $t$, multiple $R$, etc. statistics.
Waldir Leoncio
- 2,137
- 6
- 28
- 42
steve
- 11
- 1
-
-
https://www.youtube.com/watch?v=aMJOgopkLeY This is a really good video that walks through from raw data to regression output in excel using matrices. I was trying to find out how to work it from a covariance matrix since I have some missing data (pairwise) and from raw data will not work in Excel. You'll notice that one of the intervening steps he has is the covariance X1-Xi matrix - it just wasnt labeled as such so it took me a while to figure out that's what it was. He shows in detail how to use the minverse and mmult functions as well. – steve May 29 '18 at 12:46