Hi,
I'd like to think myself pretty proficient in Excel and VBA (partly thanks to being able to google answers so easily thanks to fantastic communities like this), but I'm really stumped on this one.
I have a large data table with different measures as columns (column titles in row 5), and different countries as rows (from 101 to 154). Beneath that table I wanted to populate a table of the Correlation co-efficient for each pair of columns (i.e. every measure listed in each row and each column).
My first approach was to create all the row and column titles (each measure), with row titles going from A201 down and column titles going from B200 across. Therfore, A201=B200; A202=C200... I then used the following formula in the first cell of the table, B201:
=CORREL(INDIRECT(ADDRESS(101,MATCH(B$200,$5:$5,0))&":"&ADDRESS(154,MATCH(B$200,$5:$5,0))),INDIRECT(ADDRESS(101,MATCH($A201,$5:$5,0))&":"&ADDRESS(154,MATCH($A201,$5:$5,0))))
The above formula works perfectly. But then I realised the way I have created the table means that not all column titles in row 5 are unique. I don't want to manually change this either as these are dynamic. So I thought I'd stop using match and since the correlation table appears directly below the original table, I could simply use row() and column():
=CORREL(INDIRECT(ADDRESS(101,COLUMN())&":"&ADDRESS(154,COLUMN())),INDIRECT(ADDRESS(101,ROW()-199)&":"&ADDRESS(154,ROW()-199)))
But when I hit enter, the result is #NUM!
I've used the Excel Fx button to inspect each part of each of the two formulas, and they both resolves each section precisely the same. I presume that row() and column() are not allowed as part of array parameters perhaps, but couldn't find any diffinitive answer on it. If someone could put me out of my misery that would be amazing!
Thanks in advance
Bookmarks