# Dynamic Correlation Table

1. ## 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.

2. ## 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))

3. ## 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. ## 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.

5. ## Re: Dynamic Correlation Table

Brilliant thanks so much

6. ## 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.

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

#### 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