Data Problem with Tables - Correlation and Standart Deviation

[Henrique Carvalho]

Hi Guys!


I have a table with the mensal variation of some assets. The period is 1999-2010.


I´d already calculated the annual compound growth for each year with sucess, using sumproduct formula to identify what the year is and then return only the data that i want.


Although, I´m having some problems trying to figure out what is the correlation and the standart deviation between 2 assets for a given year.


I´ve done my research here in the forum and in this site, but couldn´t find a anwser for this data problem.


I´d uploaded the file (xlsx) for better analysis.


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


Thanks in Advance!

 

[Hui]

I would change your years in the Correlation and Std Deviation tables to dates like 1/1/1999, 1/1/2000 etc and change the Number Format as YYYY

then use in


J25:

=CORREL(OFFSET(Dados[[#Headers],[IPCA]],MATCH(H25,Dados[Mês],0),0,COUNTIFS(Dados[Mês],">="&H25,Dados[Mês],"<"&H26)),OFFSET(Dados[[#Headers],[IGPM]],MATCH(H25,Dados[Mês],0),0,COUNTIFS(Dados[Mês],">="&H25,Dados[Mês],"<"&H26)))


N25: =STDEV(OFFSET(Dados[[#Headers],[IPCA]],MATCH(L25,Dados[Mês],0),0,COUNTIFS(Dados[Mês],">="&L25,Dados[Mês],"<"&L26)))*SQRT(12)

and copy down

 

[Henrique Carvalho]

Wow! Thanks Hui!


Worked Perfectly!