Applying Linear Regression Function Easily

[YaoPau]

I use LINEST to perform linear regression. Once I get the coefficient estimates, I find the y-hat values by inserting a formula into a new column, something like this:

=$W$1+$V$1*A2+$U$1*B2+$T$1*C2

where W1, V1, U1, T1 are the B0, B1, B2, B3 values ... and A2, B2, C2 are the X1, X2, X3 values respectively for the first observation. And then I fill down so each observation has its matched y-hat value.

That works efficiently for me when there are a few predictor variables. But I am working with a dataset now with about 100 predictor variables, and it is a real pain to type in an extended formula. What is an easier way for me to create a column of y-hats?

 

[AlphaFrog]

I don't follow everything going on, but here's a guess.

The SUMPRODUCT function takes 1 or more arrays of numbers and gets the sum of products. So this...
=SUMPRODUCT($T$1:$V$1,A2:C2)

...would be the equivalent to
=T1*A2+U1*B2+V1*C2

It looks like you want to reverse the order of T1:V1. You cannot revese order a range by using...
=SUMPRODUCT($V$1:$T$1,A2:C2)

You would have to use something like...
=SUMPRODUCT(N(OFFSET($T$1,0,COLUMN($V$1)-COLUMN($T$1:$V$1))),A2:C2)

So this would be the equivalent of your formula...

=$W$1+SUMPRODUCT(N(OFFSET($T$1,0,COLUMN($V$1)-COLUMN($T$1:$V$1))),A2:C2)

This was a total guess as to what you want. Am I even close?

 

[YaoPau]

Nice guess, that worked perfectly Thank you.

There has to be a less advanced way though? To clarify with a more simple example, I have the following values in A1:D5...

Y1 X0 X1 X2
2 1 1 9
9 1 2 20
1 1 0 20
4 1 2 10

Then I highlighted H2:J5, and typed =LINEST(A2:A5,C2:D5,,TRUE) into H2 then pressed CTRL + SHIFT + ENTER. I got -5.54 in J2, 3.51 in I2, and 0.35 in H2.

I want to put the Y-hat values in column E, and so for the first row we get: -5.54 * (1) + 3.51 * (1) + 0.35 * 9.

Your way works, and if that's the easiest way that's perfectly fine and it will save me a lot of time with this project. But I expected Excel to store the function somewhere, or at least store the fit values somewhere. Kinda seems pointless to have a LINEST function and then make the user type in so much to use the data.

 

[pgc01]

Hi

Another option is to use Trend(). For your example, try:

=TREND($A$2:$A$5,$C$2:$D$5,C2:D2)

 

[PaddyD]

Hi. No idea, but you might find something here useful:

http://www.tushar-mehta.com/publish_train/data_analysis/16.htm