Problem with INDIRECT arguments in the CORREL function

[NeverLift]

The situation: I have three columns, say A, G, Q; the first, A,
contains dates, G and Q are values on those dates. They are in
sequence by date. The data for each starts in row 14 (the prior rows
contain various labels, functions, etc.) I would like to determine
the correlation -- CORREL function -- of subsets of the data in G and
Q by entering a start date into a control cell, A6 and having the
CORREL function ranges automatically adjusted to start with the first
entry on that date. (Assume the data do not go past row 3000, with
unused rows at the end empty.)

So, I created the following formula:

=CORREL(INDIRECT("Q"&(MATCH($A$6,$A14:$A3000,0)+ROW($A13))&":Q3000"),
INDIRECT("G"&(MATCH($A$6,$A14:$A3000,0)+ROW($A13))&":G3000"))

The use of the ROW function is to ensure that the equation is adjusted
automatically if additional rows were to be inserted into the header
region later; using "+13" instead would not do this. (I'm not worried
about inserting additional columns, so "G" and "Q" are named
explicitly.) This construct is working fine in other formulas.

But: I'm getting a #NUM error here. When I enter a formula that
cites specific ranges, the value is computed just fine. Moreover, the
following works as well:

In cell B1: ="Q"&(MATCH($A$6,$A14:$A3000,0)+ROW($A13))&":Q3000"
In cell B2: ="G"&(MATCH($A$6,$A14:$A3000,0)+ROW($A13))&":G3000"

I can then see that the text in B1 and B2 selects the desired rows of
columns Q and G. And, =CORREL(INDIRECT(B1),INDIRECT(B2)) works just
fine!

As an experiment, I tried

=SUM(INDIRECT("Q"&(MATCH($A$6,$A14:$A3000,0)+ROW($A13))&":Q3000"))

which uses exactly the same computed range spec, character for
character (it was pasted) as the first argument of CORREL; it works
with no problem. Same for column G.

So, what is wrong with the original CORREL construct?

Thanks,

Gary

 

[Harlan Grove]

...
...

=CORREL(INDIRECT("Q"&(MATCH($A$6,$A14:$A3000,0)+ROW($A13))&":Q3000"),
INDIRECT("G"&(MATCH($A$6,$A14:$A3000,0)+ROW($A13))&":G3000")) ...
But: I'm getting a #NUM error here. . . .

...

Believe it or not, this problem is due to using ROW(.) inside INDIRECT. Using
the formula bar to evaluate pieces of this formula, select

"Q"&(MATCH($A$6,$A14:$A3000,0)+ROW($A13))&":Q3000"

and press [F9]. The result is {"Q#:Q3000"} rather than "Q#:Q3000". INDIRECT fed
this 1-entry array of textrefs does evaluate to a 1-entry *array* of range
references. Unfortunately, CORREL can't work with arrays of range references,
even single entry ones.

Try this instead.

=CORREL(INDIRECT("Q"&(MATCH($A$6,$A14:$A3000,0)+CELL("Row",$A14)-1)&":Q3000"),
INDIRECT("G"&(MATCH($A$6,$A14:$A3000,0)+CELL("Row",$A14)-1)&":G3000"))

[Yes, I'm aware I changed the correction term from A13 to A14, which then
requires the '-1'. If you want robust, this handles rows inserted between rows
13 and 14 - your formula wouldn't.]