Re: Problem with array ref (I think!)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

--
Biff
Microsoft Excel MVP


"T. Valko" <biffinpitt@xxxxxxxxxxx> wrote in message
news:etaAE5B%23JHA.1488@xxxxxxxxxxxxxxxxxxxxxxx
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))

--
Biff
Microsoft Excel MVP


"Shane Devenshire" <ShaneDevenshire@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:EC7FF88C-238A-4356-8033-7F7E29CC5DB4@xxxxxxxxxxxxxxxx
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.






-----------------------------------------------------------------------------
Our Peering Chose change
Visit : http://spacesst.com/peerin
.

Relevant Pages

  • Re: Problem with array ref (I think!)
    ... Microsoft Excel MVP ... *always* returns an array even if the reference is to a single element. ... ROW is taking the single cell ref and making it an array. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Problem with array ref (I think!)
    ... Microsoft Excel MVP ... *always* returns an array even if the reference is to a single element. ... ROW is taking the single cell ref and making it an array. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: COUNTIFs Based on Field Headings
    ... Microsoft Excel MVP ... AND interpretation to an OR interpretation? ... that the counts (ranks) are consecutive. ... This expression will return an array of either TRUE or FALSE: ...
    (microsoft.public.excel.misc)
  • Re: Copy/Paste array formulae from the newsgroup
    ... Microsoft Excel MVP ... press F2 and commit with Enter ... has not been committed as an array. ... This due to what's called the implicit intersection rule in regards to ...
    (microsoft.public.excel.misc)
  • Re: Need help with INDEX lookup
    ... Valko" wrote: ... on another sheet does every array reference ... if all the data was on a different sheet you need to include ... Microsoft Excel MVP ...
    (microsoft.public.excel.worksheet.functions)