Regression Coefficients

[excelV]

I'm trying to build a macro with regression statistics for multivariables. I tried "recording" the regression button from data analysis and manipulating the code but continuously keep getting errors.

All I need are the coefficients so I can build an equation for new data. I need to do this almost 1000 times so I need to know an efficient way to do this.

I would really appreciate it if someone can help me out, thanks!

 

[Rasm]

Are you using the LinEst function ? - maybe post your code - is this a multivariate space or just a single array of X values - you said Coefficeents in pural - so sounds like you have several. Maybe post a sample sample set with the Y and Xs columns.

 

[excelV]

No I am not familiar with the LinEst function, does it calculate the coefficients?

Yes I have one Y variable and two X variables




Date Y X1 X2
10/19/2007 0.046061508 0.045923219 0.064031621
10/23/2007 0.048873358 0.046061508 0.045923219
10/24/2007 0.048647755 0.048873358 0.046061508
11/2/2007 0.045373488 0.048647755 0.048873358
11/29/2007 0.047717659 0.045373488 0.048647755

 

[shg]

      ----A----- ---B--- ---C--- ---D--- ---E--- F G ---H---- ---I---- ---J---
  1      Date       Y      X1      X2      Fit                                
  2   10/19/2007 0.04606 0.04592 0.06403 0.04573     -0.15101 -0.38699 0.07317
  3   10/23/2007 0.04887 0.04606 0.04592 0.04841                              
  4   10/24/2007 0.04865 0.04887 0.04606 0.04730                              
  5   11/2/2007  0.04537 0.04865 0.04887 0.04696                              
  6   11/29/2007 0.04772 0.04537 0.04865 0.04827

The array formula in H2:J2 is

=LINEST(B2:B6, C2:D6)

The formula in E2 and own is

=$H$2*D2 + $I$2*C2 + $J$2

 

[excelV]

Thanks!

 

[Rasm]

If you look at help for LinEst - you will see you can set the stats to True - this gives you F-Constants - RSQ, SEE and so on - not sure if you just want coef