Applying Linear Regression Function Easily

YaoPau

Board Regular
Joined
Jun 11, 2009
Messages
54
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?
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
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...
Code:
=$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

Board Regular
Joined
Jun 11, 2009
Messages
54
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi

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

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

Watch MrExcel Video

Forum statistics

Threads
1,127,205
Messages
5,623,363
Members
415,969
Latest member
Rey99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top