Correlation - Return the assets that have the bigger correlation

[Henrique Carvalho]

Hi Guys!


I´ve already done all the procedures:


1. Calculate the correlation between assets

2. Find the top 5 larger values and the top 5 smaller values.


Instead, i´ve had no sucess returning what are the assets that match this top 5 correlations.


I´m sure that this is a problem of reference, but match, index formulas dindn´t work.


Can you have a look in the spreadsheet please? All the instructions are there.


http://www.megaupload.com/?d=YN3H8814


Thanks in advance!

 

[Hui]

Henrique

in M17 try this beasty:

=INDEX($K$5:$K$11,(ROW($K$11)*IFERROR(MATCH($L17,$L$11:$R$11,0)>0,0))+(ROW($K$10)*IFERROR(MATCH($L17,$L$10:$R$10,0)>0,0))+(ROW($K$9)*IFERROR(MATCH($L17,$L$9:$R$9,0)>0,0))+(ROW($K$8)*IFERROR(MATCH($L17,$L$8:$R$8,0)>0,0))+(ROW($K$7)*IFERROR(MATCH($L17,$L$7:$R$7,0)>0,0))+(ROW($K$6)*IFERROR(MATCH($L17,$L$6:$R$6,0)>0,0))+(ROW($K$5)*IFERROR(MATCH($L17,$L$5:$R$5,0)>0,0))-4)


and in N17 his mate:

=INDEX($L$4:$R$4,0,(COLUMN($R$4)*IFERROR(MATCH($L17,$R$5:$R$11,0)>0,0))+(COLUMN($Q$4)*IFERROR(MATCH($L17,$Q$5:$Q$11,0)>0,0))+(COLUMN($P$4)*IFERROR(MATCH($L17,$P$5:$P$11,0)>0,0))+(COLUMN($O$4)*IFERROR(MATCH($L17,$O$5:$O$11,0)>0,0))+(COLUMN($N$4)*IFERROR(MATCH($L17,$N$5:$N$11,0)>0,0))+(COLUMN($M$4)*IFERROR(MATCH($L17,$M$5:$M$11,0)>0,0))+(COLUMN($L$4)*IFERROR(MATCH($L17,$L$5:$L$11,0)>0,0))-11)

Then copy both down


You can also copy these to the other areas and then adjust the lookup cell accordingly

 

[Henrique Carvalho]

Wow! You´re tha Man Hui!


That´s a freaking nice Beasty!


Worked very well. Thanks!