+ Reply to Thread
Results 1 to 6 of 6

Dynamic Correlation Table

  1. #1
    Forum Contributor
    Join Date
    11-13-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    279

    Dynamic Correlation Table

    I know the data analysis toolpak enables a stupid hardcoded correlation table to be produced, but does anybody have any ideas of how to write a formula in the cell b8 that can be dragged to fill out the table and automatically calculate the correlations of the data set.

    At the moment I am manually typing in formulas to calculate correlations which is slow inefficient and tedious.

    Thanks so much. File attached.
    Attached Files Attached Files

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,672

    Re: Dynamic Correlation Table

    Please Login or Register  to view this content.
    B8 and copy down and across,

    =CORREL($B1:$G1, INDEX($B$1:$G$4, COLUMNS($A8:B8) - 1, 0))
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Contributor
    Join Date
    11-13-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    279

    Re: Dynamic Correlation Table

    Thanks, that's interesting, can I ask how the COLUMNS($A8:B8) is working, it seems quite strange to me, and those #DIV/0!s... they are not a mistake write? thanks for this, I assume Im being stupid but ive never used columns before so i'm a little scared by it, thanks

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,672

    Re: Dynamic Correlation Table

    can I ask how the COLUMNS($A8:B8) is working
    It's straightforward. Read about it in Help, watch it evaluate with the Evaluate Formula button, and post back if you don't understand.

    Please Login or Register  to view this content.
    Because the CORREL function ends up with a zero in the denominator if the arguments include series B. It's meaningless to ask how a constant series correlates with a time-varying one.
    Last edited by shg; 07-05-2013 at 12:35 PM.

  5. #5
    Forum Contributor
    Join Date
    11-13-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    279

    Re: Dynamic Correlation Table

    Brilliant thanks so much

  6. #6
    Registered User
    Join Date
    12-21-2013
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2016
    Posts
    46

    Re: Dynamic Correlation Table

    Hi

    I want correlation Matrix. My data is sheet called CLOSE and want correlation matrix in sheet called CORREL MATRIX. CLOSE sheet having header and data starts from A2 to AM21. My data auto updated daily but no change in header and range. I want martix table in sheet CORREL MARTIX. The result should change as and when daily data get updated.

    For business purpose, I have to use excel 2003. I tried excel data analysis tool pack but it did not refresh result as and when my data get updated.

    Thanks and Regards.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1