• 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.

Help needed for correlation problem

K

kirkmaciano

New Member
Hi all excel experts


I have 3 columns - Date | AUDUSD price | EURJPY price


I am looking to find the correlation of the closing price between AUDUSD and EURJPY for the last 40 days. I can manage to do that with a simple correl function.


However when I entered a new closing price for the day, I would like the correl function to take into account only from the 2nd day to the 41st day of the data. I was thinking of match() and row() but I couldn't managed to get it work.


Appreciate some help or links to the thread (if this has been answered before).


Thanks

Kirk
 
Kirk


Use named formula for the inputs to the Correlation function


Setup 2 Named Formula like:


Aud:
Code: 
=OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$B:$B)-40,,40)

Eur: [code]=OFFSET(Aud,,1)


Then in the Correl() function use =CORREL(Aud,Eur)[/code]


Adjust Column B to suit


You can see an example here: https://www.dropbox.com/s/k3gappnhidecwr0/Correl%20Example.xls


If this doesn't help maybe post a sample file: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi Hui


It works although I don't really understand how the formula works.


This is the second time you have helped me. Thanks for your help.


Have a good day.
 
Kirk


Have a read about Dynamic Ranges, here: http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/


Then just work through the formulas slowly
 
PHOTOS: Reese's super-chic pregnancy style A source tells Us Weekly Witherspoon and her CAA agent husband Jim Toth,Isabel Marant Sneakers Bekket High-top Suede Red White Blue," the insider said after Us Weekly broke news of Witherspoon's pregnancy March 21. Low assistive technology devices include support gadgets that do not involve electronics and do not require a battery for operation." she said. who was also previously linked to Brody Jenner and Derek Hough explained. there could be limitations in the technology,Isabel Marant Sneakers Bekket High-top Suede Black, Medical information could also be made accurate and reliable. It is.
Sharpen on the quantity prime,Isabel Marant Sneakers Bekket High-top Suede White Beige, Miranda Lambert's Pistol Annies," Urban says he had issues with his vocal cords for years.

Related articles：

 
You must log in or register to reply here.
Back
Top