Problem with array ref (I think!)

[Nick H]

I have two columns of numbers, and I want to calculate the slope of the
regression line for the data from the current row to the bottom of the table.
I built this formula:

=SLOPE(INDIRECT("$L"&TEXT(ROW(),0)&":$L$"&TEXT($B$9,0)),INDIRECT("$A"&TEXT(ROW(),0)&":$A$"&TEXT($B$9,0)))

The y values are in column L, the x values in column A, and the row number
of the bottom of the table is in cell B9.

I'm getting #NUM; when I step through the error analysis, it seems like I'm
getting valid ranges. Can anyone help me with this? Thanks.

 

[Shane Devenshire]

Hi,

The ROW() function collapses the array to a single cell. To get around
this try

=SLOPE(INDIRECT("A"&CELL("row")&":A"&B9),INDIRECT("L"&CELL("row")&":L"&B9))

 

[T. Valko]

The ROW() function collapses the array to a single cell.

Then why does this work:

=SUM(INDIRECT("$L"&TEXT(ROW(),0)&":$L$"&TEXT($B$9,0)),INDIRECT("$A"&TEXT(ROW(),0)&":$A$"&TEXT($B$9,0)))

You solution will work but your reasoning is backwards. The ROW function
*always* returns an array even if the reference is to a single element. So
ROW is taking the single cell ref and making it an array. What's happening
seems to be function specific. SLOPE maintains the ROW reference as an array
but other functions, like SUM, will "convert" the array to a non-array
single element.

Here's another way:

=SLOPE(INDIRECT("L"&INDEX(ROW(),1)&":L"&B9),INDIRECT("A"&INDEX(ROW(),1)&":A"&B9))

 

[T. Valko]

While we're at it, this can be done without the use of volatile functions:

=SLOPE(INDEX(L:L,ROW()):INDEX(L:L,B9),INDEX(A:A,ROW()):INDEX(A:A,B9))

 

[Shane Devenshire]

I'm sure you are aware of other examples of this but enter

=INDIRECT("$L"&TEXT(ROW(),0)&":$L$"&TEXT($B$9,0))

assume you have values in the range L2:L50
select it and press F9. You will see the single element {2} or some other
number.

now enter =SUM(INDIRECT("$L"&TEXT(ROW(),0)&":$L$"&TEXT($B$9,0)))
not 2 anymore.

Why, I suppose you could say because Microsoft programmed it that way.

Try entering
=Sheet1:Sheet5!A1:A10 and of course you get #REF!
Put a SUM around it and assuming you have numerical data in the ranges
A1:A10 you will get a result. If in the formula =SUM(Sheet1:Sheet5!A1:A10)
you highlight the interior and press F9 you are back to REF!. So the SUM of
REF is a value. In fact in most places in Excel summing a cell with an error
will propogate the error. Why doesn't Microsoft allow us to see the
elements? Of course its a 3D range but still they could let us see it. In
fact why don't they give us access to the 3D grid with all functions which
support ranges?

 

[Shane Devenshire]

Hi,

More importantly my formula reversed the ranges column A for L so it should
read:

=SLOPE(INDIRECT("L"&CELL("row")&":L"&B9),INDIRECT("A"&CELL("row")&":A"&B9))

Of course INDEX is volatile or not depending on the version. Microsoft
changed it a while back.

 

[Shane Devenshire]

Hi Nick,

Please disregard my suggestion, although it worked in testing there are some
problems with it, to say nothing of the fact that I got the arguments in the
incorrect order.

 

[Harlan Grove]

now enter =SUM(INDIRECT("$L"&TEXT(ROW(),0)&":$L$"&TEXT($B$9,0)))
not 2 anymore.

Why, I suppose you could say because Microsoft programmed it that way.

....

The functions which accept variable numbers of arguments with the
exceptions of CONCATENATE, CHOOSE and NPV *ALWAYS* return single
results because those functions' semantics *REQUIRE* them to return
single values. I call these the aggregating functions. Getting
technical, they produce scalar descriptive statistics from arbitrary
collections of data values.

NPV is the joker in the group. If its 1st argument is a single area
range or array, then NPV returns an array result with the same size/
shape as its 1st argument with the array result being the NPVs of the
subsequent arguments using each of the interest rates in the 1st
argument separately.

Anyway, ROW() always returns an array, and that often causes problems
in dynamic range constructs. Excel seems to treat such constructs as
arrays of range references. Speculation: the aggregating functions
handle them because they're built to handle arbitrary iteration both
over all of and within each of their arguments. Most other functions
are more delicate and choke on such argument values.

Maybe a different indicator would be functions which can handle
multiple area ranges (other than AREAS, CELL, CHOOSE and INDEX) can
handle arrays of range references.

I've found it much safer to use ROWS rather than ROW. For example, if
you want to use ROW() in cell X99, you could use ROWS($A$1:X99) or ROWS
(INDEX($1:$65536,1,1):X99) instead. The latter construct is immune to
row/column insertion/deletion other than deleting row 99 or column X,
which would blast the formula out of existence anyway. ROWS, unlike
ROW, always returns a scalar, i.e., a single, nonarray value.

IMO, it's unwise to use ROW except when you want to return multiple-
value array results.

 

[Harlan Grove]

=SLOPE(INDIRECT("L"&CELL("row")&":L"&B9),INDIRECT("A"&CELL("row")&":A"&B9))

....

This is an error-in-waiting. ROW() returns the row (as a single-value
array) of the cell containing the formula calling ROW(). CELL("row")
returns the row of the active cell when recalculation occurs. Not the
same thing.

Isn't this a rookie mistake?

 

[Nick H]

Thanks, this is both useful and elegant. I had some trouble at first because
I had the formula in column L (so ti produced a circular) but that was an
easy fix. Thanks again!

 

[T. Valko]

You're welcome. Thanks for the feedback!