Estimating a line of regression

**triads** · 04-27-2009, 05:48 AM

Hi ive been asked to
estiamte the regression line y = α + βx+ ε

i have 2 sets of data
y and x
how can i do this in excel
thanks for any advice

**zbor** · 04-27-2009, 06:11 AM

You can write that:

β= (AVERAGE(B1:B29)-B1)/(AVERAGE(A1:A29)-A1)
α + ε =AVERAGE(B1:B29)-β*AVERAGE(A1:A29) or =AVERAGE(B1:B29)-(AVERAGE(B1:B29)-A1)/(AVERAGE(A1:A29)-B1)*AVERAGE(A1:A29)

**zbor** · 04-27-2009, 06:17 AM

This is explanation of formula... of course, you can use:

=TREND($B$1:$B$29;$A$1:$A$29;A30) where A30 is new value and B1:B29 are y values and A:A29 are x values

**triads** · 04-27-2009, 06:22 AM

is there no way of using the regression tool in the data analysis tab?
and you said A30 is the new value, im not looking for a new value, so i dont know what to put there
thanks to both of you for the responses

**zbor** · 04-27-2009, 06:28 AM

You can try with =LINEST(B1:B29;A1:A29) wich will give you β

**triads** · 04-27-2009, 08:39 AM

It might be easier if i give you the 2 sets of data

y
11.2
11.05
6.84
9.21
9.42
10.08
9.45
6.73
7.24
6.12
7.63
9.43
9.46
7.64
6.92
8.95
9.33
10.23
11.77
7.41

x
907
926
506
741
789
889
874
510
529
420
679
872
924
607
452
729
794
844
1010
621

Y is sales X is customers
so what is the regression line y = α + βx+ ε
of these 2 sets of data?
thanks again

**shg** · 04-27-2009, 08:43 AM

For a linear regression, there are just two parameters of the equation y = mx + b, m and b. If the x data is in A1:A10, and the y data in B1:B10, then select TWO cells, e.g., C1 and D1, and paste this in this formula bar:

=LINEST(B1:B10, A1:A10)

Then INSTEAD of pressing Enter, press and hold the Ctrl and Shift keys, then press Enter. (That's how an array formula is entered.)

C1 has the slope and D1 the intercept.