Linest function

[Barna]

I am trying to nest other formulas into Linest with no success. I am trying
to set it up so users can change the range of cells that they want to run the
regression on without having to actually step into the formula.
This is an example of a formula:
=LINEST(Pivots!H28:H51,Pivots!F28:G51,TRUE,TRUE)
Can I embed another formula into Linest that would automatically change the
range?
Thank you,

Barna

PS: I am using Excel 2003

 

[Harlan Grove]

I am trying to set it up so users can change the range of cells
that they want to run the regression on without having to actually
step into the formula. ....
Can I embed another formula into Linest that would automatically
change the range?

....

Yes. LINEST needs single area ranges or arrays as 1st and 2nd
arguments, and any expressions that evaluate to single area ranges or
arrays could be used.

If you have the following named cells,

YCol column number of Y range
YTop row number of top row in Y range
YBot row number of bottom row in Y range

XLft column number of leftmost column in X range
XRgt column number of rightmost column in X range
XTop row number of top row in X range
XBot row number of bottom row in X range

Data refers to the entire worksheet containing X and Y ranges,
e.g., Data!1:65536

you could use these defined names to build dynamic range references
that could be used in LINEST calls.

=LINEST(INDEX(Data,YTop,YCol):INDEX(Data,YBot,YCol),
INDEX(Data,XTop,XLft):INDEX(Data,XBot,XRgt),...)

Now XTop and YTop etc would likely be the same, so XTop and XBot could
just be simple references to YTop and YBot, respectively, but this
allows for some flexibility. The only thing that's absolutely
necessary is that XBot - XTop = YBot - YTop, i.e., X and Y ranges have
the same number of rows, so XBot might as well be defined as

=XTop+YBot-YTop