Correlation Matrix
you rock!!! thanks that worked
"Domenic" wrote:
Assumptions:
Source table...
A1:C1 contains IBM, TGT, and HD
A2:C4 contains your data
Results table...
F1:H1 contains IBM, TGT, and HD
E2:E4 contains IBM, TGT, and HD
Formula:
F2, copied down and across:
=CORREL(INDEX($A$2:$C$4,0,MATCH($E2,$A$1:$C$1,0)), INDEX($A$2:$C$4,0,MATCH
(F$1,$A$1:$C$1,0)))
If you want the formula to leave the cell empty when correlating the
same security, try the following formula instead...
=IF($E2<F$1,CORREL(INDEX($A$2:$C$4,0,MATCH($E2,$A $1:$C$1,0)),INDEX($A$2:
$C$4,0,MATCH(F$1,$A$1:$C$1,0))),"")
Adjust the ranges accordingly.
Hope this helps!
In article ,
"katie" wrote:
I have 30 securities with 5 years on monthly returns
Securities across row A and returns down the columns
A B C D
1 IBM TGT HD
2 1 3 2
3 -.5 2 -1
4 2 6 -3
I want to create a matrix that correlates the return of every security
against every securit
IBM TGT HD
IBM
TGT
HD
Is there a way to put a look up function into a correlation furnction when
you want it to supply you with an array?
=Correl(lookup (IBM,other work sheet row A, give array set below IBM in
other work sheet), lookup TGT, other work sheet row a, give array set below
TGT in other worksheet)
This type of formula is not working for me so any suggestions would be great.
|