Problem with array ref (I think!)
 

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.

Problem with array ref (I think!)
 

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))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Nick H" wrote:

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.

Problem with array ref (I think!)
 

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),INDIRE CT("A"&INDEX(ROW(),1)&":A"&B9))

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
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))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Nick H" wrote:

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.

Problem with array ref (I think!)
 

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,RO W()):INDEX(A:A,B9))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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),INDIRE CT("A"&INDEX(ROW(),1)&":A"&B9))

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
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))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Nick H" wrote:

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.

Problem with array ref (I think!)
 

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?

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"T. Valko" wrote:

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),INDIRE CT("A"&INDEX(ROW(),1)&":A"&B9))

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
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))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Nick H" wrote:

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.

Problem with array ref (I think!)
 

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"T. Valko" wrote:

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,RO W()):INDEX(A:A,B9))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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),INDIRE CT("A"&INDEX(ROW(),1)&":A"&B9))

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
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))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Nick H" wrote:

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.

Problem with array ref (I think!)
 

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Nick H" wrote:

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.

Problem with array ref (I think!)
 

Shane Devenshire wrote...
....
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.

Problem with array ref (I think!)
 

Shane Devenshire wrote...
....
=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?

Problem with array ref (I think!)
 

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" wrote:

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,RO W()):INDEX(A:A,B9))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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),INDIRE CT("A"&INDEX(ROW(),1)&":A"&B9))

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
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))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Nick H" wrote:

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.

Problem with array ref (I think!)
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Nick H" wrote in message
...
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" wrote:

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,RO W()):INDEX(A:A,B9))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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),INDIRE CT("A"&INDEX(ROW(),1)&":A"&B9))

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
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))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Nick H" wrote:

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.