Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
buragotch
 
Posts: n/a
Default 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
  #2   Report Post  
Tushar Mehta
 
Posts: n/a
Default

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

  #3   Report Post  
buragotch
 
Posts: n/a
Default

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


  #4   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

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


  #5   Report Post  
Tushar Mehta
 
Posts: n/a
Default

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Table with Zero Values for Month wyman Charts and Charting in Excel 1 January 14th 05 06:59 PM
fill formulas and values Jose Mourinho Excel Discussion (Misc queries) 1 January 4th 05 04:05 PM
Sum minimum values in a pivot table AK Excel Worksheet Functions 1 December 22nd 04 09:55 PM
To find different values in Col B corresp. to repeated vaues in c K.S.Warrier Excel Worksheet Functions 7 December 10th 04 11:57 AM
Identifying the Active Fill Color Steve Conary Excel Discussion (Misc queries) 3 December 9th 04 05:45 AM


All times are GMT +1. The time now is 09:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"