+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Registered User
    Join Date
    10-24-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    10

    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

  2. #2
    Registered User
    Join Date
    10-24-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    10

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

    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.

  3. #3
    Registered User
    Join Date
    10-24-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    10

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

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

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,186

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

    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
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  5. #5
    Registered User
    Join Date
    10-24-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    10

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

    Quote Originally Posted by XOR LX View Post
    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
    Attached Files Attached Files

  6. #6
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,186

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

    Thanks.

    There's a lot to discuss here. Firstly, the use of ROW() and COLUMN() in their unqualified form is not at all the best method in such circumstances, as both suffer drawbacks that alternatives (ROWS and COLUMNS) do not. See here for more:

    http://excelxor.com/2014/08/25/row-v...er-generation/

    What's more, you have encountered a further issue by using these functions here, and one which is not so straightforward to explain, and that is that certain functions, CORREL for example, expect the arguments passed to them to be, not single values, but arrays of values. Of course, you're more than likely perfectly aware of this, since using such a function over two sets containing just a single value each would not have much statistical significance.

    This has certain technical implications here: since ROW and COLUMN are functions which can equally return an array of values as they can a single value, CORREL, as mentioned expecting that array of values, is applying array-coercion to the constructions involved (some functions require CTRL+SHIFT+ENTER here; CORREL has the ability to induce arrays without it), and this array-coercion is equally applied to the ROW() or COLUMN() function within that construction.

    Unfortunately, this means that, although the output of those functions are here just a single value, their type, in technical terms as considered by Excel, is actually an array of values (albeit an array containing a single value only).

    Hence, if you evaluate your formula, you'll see that this part:

    INDIRECT(ADDRESS(101,COLUMN())&":"&ADDRESS(154,COLUMN()))

    resolves as:

    INDIRECT(ADDRESS(101,{2})&":"&ADDRESS(154,{2}))

    (Note the curly brackets around the {2} for the resolution of the COLUMN() portion, which indicates that this value is an array, albeit, as mentioned, containing a single value only.)

    This then becomes:

    INDIRECT({"$B$101"}&":"&{"$B$154"})

    which is:

    INDIRECT({"$B$101:$B$154"})

    and, since the reference being passed to INDIRECT is technically an array, the formula errors. Note that, if the resolution had been:

    INDIRECT("$B$101:$B$154")

    you would have faced no problems at all.

    One way to resolve this issue is to force the array into a single value using an additional operation, for example SUM, since:

    SUM(COLUMN())

    would here resolve to:

    SUM({2})

    which is then simply the non-array 2, and so can be passed to ADDRESS and INDIRECT without any issues.

    Better still is, as mentioned, the use of COLUMNS, not the unqualified COLUMN: since COLUMNS, unlike COLUMN, is not capable of returning an array of values, CORREL would not be induced into attempting to make it do so, and so something like:

    INDIRECT(ADDRESS(101,COLUMNS($A:B))&":"&ADDRESS(154,COLUMNS($A:B)))

    would resolve to:

    INDIRECT(ADDRESS(101,2)&":"&ADDRESS(154,2))

    This time the 2 is not of an array data type, and this will resolve as desired.

    Having said all that, none of this is necessary, as generating range references using this combination of INDIRECT and ADDRESS is almost never necessary: not only is it messy at best, but such constructions are also volatile, which can severely affect workbook performance.

    Using INDEX is the preferable method here. For example, both of these:

    INDIRECT(ADDRESS(101,COLUMNS($A:B))&":"&ADDRESS(154,COLUMNS($A:B)))

    INDEX($101:$154,,COLUMNS($A:B))

    return an array comprising the entries of the range B101:B154, though the second is vastly superior.

    Hope that helps

    Regards
    Last edited by XOR LX; 01-07-2015 at 08:25 AM.

  7. #7
    Registered User
    Join Date
    10-24-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    10

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

    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.

  8. #8
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,186

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

    You're more than welcome. And thank you for your kind feedback.

    Best of luck with the project.

    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