Dynamic Correl() formula - works with match(), fails with row() and column()

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

I think this might be of relevance: http://stackoverflow.com/questions/1...and-sumproduct but trying to wrap each row() and column() by n()... e.g. n(row()) doesn't help.

As a work around, I've entered the formula '=column()' into the row above my correlation table and '=row()' to the far right of my correlation table and using those as substitutes, but it seems daft that we need to. Fascinated to find out the answer to this if anyone knows.

Apologies for bumping this but appreciate posting on NYE probably didn't get it much visibility. Still very much stumped by this!

Originally Posted by XOR LX

Hi.

I think it's actually more the fact that you haven't provided a workbook to support your query that you haven't had much luck in terms of response.

Regards

Thanks for the pointer. I guess I thought maybe someone would recognise the problem but guess not. Have provided an example file. The formula that doesn't work appears in B201:B320. As per the text box in the file explaining the issue, the formula is designed to calculate the correlation coefficient for every pair of measures in my data table.

B201:B320 has the ideal formula which uses row() and column() to reference the ranges for the correl function. But this doesn't work, so in C201:C320 and across, I've referenced cells that hold =row() and =column(). I just wanted to understand why I couldn't use row() and column() directly in the column B formula here. As mentioned in my original post, I've tried wrapping them in N() to try and force a number but that didn't work either.

Thanks for any pointers anyone can give

I can't begin to thank you enough for such a detailed explanation taking it step by step so clearly. My knowledge and use of the index function and array functions in general is very lacking so need to improve there. I also wasn't aware of the columns() or rows() functions so thank you for sharing that too.

Really appreciate your time and hope I can repay the favour one day.