• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Problem with Tables - Correlation and Standart Deviation

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!
 
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:
Code:
=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
 
Back
Top