how can i fill a table with values from repeated regressions

[Guest]

i need to run repeated regressions on a large set of data, and put the
resulting values in a table for each column of data in my set. however, i
don't want to repeat the regression function in the data analysis toolpack
and cut/paste the relevant data into my table, 50 times.
anyone know how to do this

 

[Tushar Mehta]

Check out the LINEST and LOGEST functions. LINEST's name and
documentation are misleading. For more on how to use it for non-linear
regressions see Bernard Liengme's
Polynomial regression. How can I fit my X, Y data to a polynomial using
LINEST?
http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Guest]

Thanks for the info. However, I would like to find a way to make the LINEST
function return all its values into a single column, rather than an array, so
i can have multiple columns with all the LINEST values (eg intercept,
coefficients, r-sqaure, etc). if it use the TRANSPOSE(LINEST(...)) function,
it only returns the intercept and coefficient values. but none of the others.
there must be a way to do this.
regards,
Brad Johnson

 

[Aladin Akyurek]

Thanks for the info. However, I would like to find a way to make the LINEST
function return all its values into a single column, rather than an array, so
i can have multiple columns with all the LINEST values (eg intercept,
coefficients, r-sqaure, etc). if it use the TRANSPOSE(LINEST(...)) function,
it only returns the intercept and coefficient values. but none of the others.
there must be a way to do this.

[...]

Did you try to fill in...

LINEST(known_y's,known_x's,const,stats)

as, for example:

=LINEST(F2:F20,G2:G20,,TRUE)

which you enter say in cell A2, select the rectangular area of A2:B6,
then confirm the formula with control+shift+enter?

 

[Tushar Mehta]

Assuming you did ask for statistiscs, since LINEST returns the result
in a 5 x n array, it has to be converted to a 5n x 1 vector. One
possible way is below. The other, which might be more efficient would
be to use a UDF as a wrapper function for LINEST.

Suppose your y values are in column B and the x values in column A both
in rows 1:11. Also suppose you are attempting a 2nd order polynomial
(i.e., a quadratic) fit. Then, select 15 contiguous cells in a column
and array-enter(*)

=INDEX(LINEST(B1:B11,A1:A11^{1,2},TRUE,TRUE),INT((ROW(M1:M15)-1)/3)+
1,MOD(ROW(M1:M15)-1,3)+1)

Notes:

(1) More on this class of techniques for manipulating data in blocks is
at 'Managing data that include logical blocks' (http://www.tushar-
mehta.com/excel/tips/data_in_blocks/index.html)

(2) A quadratic fit requires a 5x3=15 cells. The references to M1:M15
are simply a way to generate the vector 1,2,...,15.

(3) For a linear fit, the default result would require 5x2=10 cells.
Change the M1:M15 to M1:M10 and the references to 3 in the divisor to 2
(there are two such references, the /3 inside the INT function and the
,3 argument of the MOD function).

(4) I don't know how XL treats this scenario in that does it call
LINEST 15 times or just once? If the former, you might be better off
writing a UDF that calls LINEST once and then reorganizes the resulting
array as needed.

(*) An array formula is entered with CTRL-SHIFT-ENTER rather than just
ENTER. If done correctly, XL will display curly brackets { and }
around the formula

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions