# Applying Linear Regression Function Easily

#### YaoPau

##### Board Regular
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?

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### AlphaFrog

##### MrExcel MVP
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
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
Hi

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

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

Replies
2
Views
135
Replies
18
Views
232
Replies
2
Views
141
Replies
0
Views
295
Replies
5
Views
426

1,127,550
Messages
5,625,448
Members
416,107
Latest member
AVaes

### 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.

### Which adblocker are you using?

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

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