Error Embedding Row() within Indirect()

[g-boy]

I have a set of values in a range of cells on a worksheet, Patterns!B2:B375

There are also values in the corresponding cells in nearby columns,
such as Patterns!C2:C375, Patterns!D2375, Patterns!E2:E375, etc.

I want to calculate the correlations between the values in the first range,
and the values in each of the other columns. And I want these correlation
values to appear on different rows (for example, in place of the ### below):

Corrleation
ColC ###
ColD ###
ColE ###

And so, I thought I could use this function:

=CORREL(INDIRECT("Patterns!R2C"&(ROW()+1)&":R375C"&(ROW()+1),FALSE),Patterns!B$2:B$375)

in each of the cells, and it would automatically have each row calculate the
correlation with a different column of values.

But... it doesn't work. It gives me #N/A.

The TRULY STRANGE thing about this, is if I simply substitute the actual row
number in place of the ROW() function, it works:

=CORREL(INDIRECT("Patterns!R2C"&(2+1)&":R375C"&(2+1),FALSE),Patterns!B$2:B$375)

This actually produces the correct numerical result. And all I did, was
replace the embedded row() function call with an actual number.

Why should I not be able to embed row() within indirect()?

---G

 

[Harlan Grove]

g-boy wrote...
....

And so, I thought I could use this function:

=CORREL(INDIRECT("Patterns!R2C"&(ROW()+1)&":R375C"&(ROW()+1),FALSE),Patterns!B$2:B$375)

in each of the cells, and it would automatically have each row calculate the
correlation with a different column of values.

But... it doesn't work. It gives me #N/A.

....

The problem is that ROW() always returns an array, even when the array
only contains a single entry. INDIRECT accepts array first arguments
and then returns undocumented things that act as if they were arrays of
range references. Those can be used only as arguments in the few
functions that require range reference arguments. CORREL isn't one of
those functions, so you need to convert the array generated by ROW() to
a scalar. The simplest way to do that is SUM(ROW()). Yes, it looks odd,
but try it.

For that matter, you'd be better off using

=CORREL(OFFSET(Patterns!$A$2:$A$375,0,SUM(ROW())),Patterns!B$2:B$375)

 

[g-boy]

[...]

The problem is that ROW() always returns an array, even when the array
only contains a single entry.

I didn't know that... I just assumed it returned the single (scalar) value
that was the row number of the cell in which the formula appears.

[...] For that matter, you'd be better off using

=CORREL(OFFSET(Patterns!$A$2:$A$375,0,SUM(ROW())),Patterns!B$2:B$375)

That's a great suggestion, thank you very much!!!

--G

 

[Harlan Grove]

I didn't know that... I just assumed it returned the single (scalar) value
that was the row number of the cell in which the formula appears.

....

When formulas don't work when you change from a hardcoded value to a
function call, you should always try evaluating the particular function call
in the formula bar. If you type =ROW() in, say, X99 and press [F9], it
becomes ={99}.

This is just another of those things that you have to learn by experience
because online help for the ROW() function certainly doesn't mention this.
Indeed, I suspect ROW() [and COLUMN()] returning single entry arrays is a
bug, but Microsoft doesn't waste money fixing bugs that would affect only
the 10% or fewer of Excel users trying to do things like this.

 

[Aladin Akyurek]

I didn't know that... I just assumed it returned the single (scalar) value
that was the row number of the cell in which the formula appears.

...

When formulas don't work when you change from a hardcoded value to a
function call, you should always try evaluating the particular function call
in the formula bar. If you type =ROW() in, say, X99 and press [F9], it
becomes ={99}.

This is just another of those things that you have to learn by experience
because online help for the ROW() function certainly doesn't mention this.
Indeed, I suspect ROW() [and COLUMN()] returning single entry arrays is a
bug, but Microsoft doesn't waste money fixing bugs that would affect only
the 10% or fewer of Excel users trying to do things like this.

Maybe

=ROW() in X99 ==> {99} instead of 99

because:

=ROW(X99:Z100) ==> {99,100}

 

[Harlan Grove]

Maybe

=ROW() in X99 ==> {99} instead of 99

because:

=ROW(X99:Z100) ==> {99,100}

Maybe on your system, but on mine ROW(X99:Z100) returns {99;100}.

Maybe it's required for consistency. ROW() in a multiple cell array formula
does evaluate to a vertical array of the rows. Nevertheless, it'd be nice if
online help mentioned that it always returns arrays.

 

[Aladin Akyurek]

Maybe on your system, but on mine ROW(X99:Z100) returns {99;100}.

On mine too.

Maybe it's required for consistency. ROW() in a multiple cell array formula
does evaluate to a vertical array of the rows.
Yep.

Nevertheless, it'd be nice if
online help mentioned that it always returns arrays.

Agree. It's a hard job to produce a full documentation on software and
the like, though.

 

[Harlan Grove]

Agree. It's a hard job to produce a full documentation on software and
the like, though.

Especially when one doesn't expend any resources on updating documentation.
Has anyone noticed any significant changes, additions or corrections in
online help in the last 3 'major' version upgrades?