An alternative is to use the Correlation option from the Analysis Toolpak.
It will return the correlation matrix, as values, not formulas.

Pieter Vandenberg

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.