how can i fill a table with values from repeated regressions
 

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

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/E...Polynomial.htm

--
Regards,

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

In article ,
says...
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

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

"Tushar Mehta" wrote:

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/E...Polynomial.htm

--
Regards,

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

In article ,
says...
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

buragotch wrote:
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?

In article ,
says...

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

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

In article ,
says...
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

"Tushar Mehta" wrote:

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/E...Polynomial.htm

--
Regards,

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

In article ,
says...
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