Compute covariance matrix using Excel
In Excel, I have 10 columns of data from column A to column J, and each column has 1000 rows from row 1 to row 1000. I wonder how to compute the 10 x 10 covariance matrix of th开发者_Python百科e 10 columns of data in Excel?
My partial solution is based on dollar signs and copying formulas:
First I input in a cell a formula =covar($A1:$A1000,A1:A1000)
.
Then I copy and paste the formula to the cells on the right of the first cell, which gives me =covar($A1:$A1000,B1:B1000)
... =covar($A1:$A1000,J1:J1000)
.
Now I don't know how I can get =covar(B1:B1000,A1:A1000)
... =covar(J1:J1000,A1:A1000)
, because if I copy and paste the formula to the cells below the first cell, I will get =covar($A2:$A1001,A2:A1001)
, ..., =covar($A1000:$A2001,A1000:A2001)
instead.
Thanks!
To make the formula "copy-proof" you can make use of the =OFFSET()
function in combination with row and column indices. Example:
- in L1...U1 enter numbers 1, 2, 3, ... 10
- in K2...K11 enter numbers 1, 2, 3, ... 10
- now copy-proof references to one of the 10 columns A...J. This can be obtained by:
=OFFSET($A$1:$A$1000,0,L$1-1)
to follow the horizontal index=OFFSET($A$1:$A$1000,0,$K2-1)
to follow the vertical index
and finally you combine the 2 above into
=COVAR(OFFSET($A$1:$A$1000,0,L$1-1),OFFSET($A$1:$A$1000,0,$K2-1))
this formula that you enter in L2, copy into L2..U11 to obtain your 10x10 matrix
Hope that helps
精彩评论