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.