| Home |
| Search |
| Today's Posts |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to identify entries in a matrix also present in another list | Excel Worksheet Functions | |||
| CORRELATION / COVARIANCE MATRIX | Excel Worksheet Functions | |||
| In Regression Analysis, How do you get a Correlation Matrix to be. | Excel Discussion (Misc queries) | |||
| Correlation matrix | Excel Worksheet Functions | |||
| Finding Values in a "Matrix" | Excel Discussion (Misc queries) | |||
Threaded Mode
