Re: Problem with array ref (I think!)
- From: "T. Valko" <biffinpitt@xxxxxxxxxxx>
- Date: Tue, 30 Jun 2009 21:00:02 -0400
You're welcome. Thanks for the feedback!
--
Biff
Microsoft Excel MVP
"Nick H" <NickH@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B57ED334-7235-49B0-A64C-0794FE6ADDFB@xxxxxxxxxxxxxxxx
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,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.
.
- References:
- Re: Problem with array ref (I think!)
- From: Nick H
- Re: Problem with array ref (I think!)
- Prev by Date: Re: Formula doesn't work unless hit enter again
- Next by Date: RE: conditional formatte date in a1 calculate the date in A2
- Previous by thread: Re: Problem with array ref (I think!)
- Next by thread: what is the Function for reducing balance depriciation?
- Index(es):
Relevant Pages
|
