Need formula operating against a dynamic range copied across a series of cells

Question

I'm creating a grid of correlation values, like a distance grid. I have a series of cells that each contain a formula whose ranges are easy to describe if you know the offset from the first cell, and I'm having trouble figuring out how to specify it.

In the upper left hand cell (R10), the formula is CORREL(C2:C21,C2:C21) -- it's 1, of course.
In the next column over (S10), the formula is CORREL(D2:D21,C2:C21).
In the next row down (R11), the formula is CORREL(C2:C21,D2:D21).
Of course, S11 would contain CORREL(D2:D21,D2:D21), which is also 1. And so on, for a roughly 15x15 grid.

Here's a graphical representation of the ranges involved:

C2:C21,C2:C21  C2:C21,D2:D21  C2:C21,E2:E21
D2:D21,C2:C21  D2:D21,D2:D21  D2:D21,E2:E21
E2:E21,C2:C21  E2:E21,D2:D21  E2:E21,E2:E21

Whenever I add a new data row, I have to manually update several formulas. So, I'd like the last non-blank column number (21, in this case), to be dynamically determined, such as with COUNTA(C:C). Ideally, I'd like the formula to calculate the row offsets, too, so that I can drag one formula across my entire range.

What's the best way to accomplish this? I think OFFSET might be a component in the solution, but I haven't had success getting it all to work together.

@pnuts, I added a more graphical representation of the ranges involved. — neontapir, May 12 '14 at 17:40
Yes, that's true. The coordinates R10 refer to the cell name in Excel, so R10 is immediately above S10. — neontapir, May 12 '14 at 17:49
R is the 18th letter of the alphabet, so it should be row 18. — neontapir, May 12 '14 at 17:52
Sorry, I must have miscalculated. I agree with you, that would be B3. C2 is in the second row, third from the left. — neontapir, May 12 '14 at 17:57
@pnuts, after solving the issue, I looked back at my text and realized the error you were trying to point out to me. Sorry I was slow on the uptake. I corrected the text. — neontapir, May 15 '14 at 04:02

score 1 · Answer 1 · answered Jun 01 '15 at 08:14

Using this simple setup per element of the corr matrix also helps:

=CORREL(INDIRECT("'Risk factors'!"&"T"&G6&":T"&H6);INDIRECT("'Risk factors'!"&"U"&G6&":U"&H6))

With this function I refer to data in another sheet, Risk factors, to correlate rows T and U with each other. I want the ranges of the data to be dynamic so I refer with G6 and H6 in my current sheet to the lenght of the columns (number of rows) which I of course specify in these G6 and H6 cells.

Hope this helps!

neontapir · Accepted Answer · 2014-05-15T04:04:10.007

I found this formula, while wordy, achieved the desired results. In this example, the data lives in C2:O19. The table I wanted to construct computed the correlation values of all permutations of pairs of columns. Since there are 11 columns, the correlation pairs table is 11x11 and starts at R10. Each cell has the following formula:

=CORREL(INDIRECT(ADDRESS(2,2+(ROWS($R$10:R10)),4)&":"&ADDRESS(COUNTA($C:$C),
2+(ROWS($R$10:R10)),4)),INDIRECT(ADDRESS(2,2+(COLUMNS($R$10:R10)),4)&":"&
ADDRESS(COUNTA($C:$C),2+(COLUMNS($R$10:R10)),4)))

As I found out, INDIRECT() resolves a cell reference and obtains its value.

Let's take a cell, say U12, and look at the range formula in detail. The first INDIRECT is the column given by applying the row offset from R10.

Since Row 12 is 2 rows down from Row 10, ADDRESS(2,2+(ROWS($R$10:U12)),4)&":"&ADDRESS(COUNTA($C:$C),2+(ROWS($R$10:U12)),4) should yield the column that's 2 rows right of Row C, which is E. The formula evaluates to E2:E19.

The second INDIRECT is the column given by applying the column offset from R10. Similarly, since Column U is 3 columns right of Column R, ADDRESS(2,2+(COLUMNS($R$10:U12)),4)&":"&ADDRESS(COUNTA($C:$C),2+(COLUMNS($R$10:U12)),4) should yield the column that's 3 rows right of Row C, which is F. The second formula evaluates to F2:F19.

Substituting these range reference values in, the cell formula reduces to =CORREL(INDIRECT("E2:E19"),INDIRECT("F2:F19")) and further to =CORREL(E2:E19,F2:F19), which is what I'd been using up till now.

Just like a distance table, this table is symmetrical along the diagonal, because =CORREL(E2:E19,F2:F19) equals =CORREL(F2:F19,E2:E19). Each value on the diagonal is 1, because CORREL of the same range is 100% correlation by definition.

Need formula operating against a dynamic range copied across a series of cells

2 Answers2