Question

Using excel, I need a simple linear regression equation to help define the relationship between height and weight using the table below. Using that equation, I need to predict the weight for a height of 5.75 inches.

Height (in feet)	Weight (in pounds)
4.8	115.10
4.9	117.11
5.0	120.11
5.1	123.11
5.2	126.10
5.2	129.12
5.3	132.12
5.4	135.12
5.5	139.13
5.6	142.13
5.7	146.12
5.7	150.13
5.8	154.15
5.9	159.15
6.0	164.16

Answer 1

	Height (X)	Weight (Y)	X * Y	X2	Y2
	4.8	115.1	552.48	23.04	13248.01
	4.9	117.11	573.839	24.01	13714.75
	5	120.11	600.55	25	14426.41
	5.1	123.11	627.861	26.01	15156.07
	5.2	126.1	655.72	27.04	15901.21
	5.2	129.12	671.424	27.04	16671.97
	5.3	132.12	700.236	28.09	17455.69
	5.4	135.12	729.648	29.16	18257.41
	5.5	139.13	765.215	30.25	19357.16
	5.6	142.13	795.928	31.36	20200.94
	5.7	146.12	832.884	32.49	21351.05
	5.7	150.13	855.741	32.49	22539.02
	5.8	154.15	894.07	33.64	23762.22
	5.9	159.15	938.985	34.81	25328.72
	6	164.16	984.96	36	26948.51
Total	81.1	2052.86	11179.54	440.43	284319.2

Equation of regression line is Ŷ = a + bX

b = ( 15 * 11179.541 - 81.1 * 2052.86 ) / ( 15 * 440.43 - ( 81.1 )²)
b = 41.251
a =( Σ Y - ( b * Σ X) ) / n
a =( 2052.86 - ( 41.2506 * 81.1 ) ) / 15
a = -86.171
Equation of regression line becomes Ŷ = -86.1712 + 41.2506 X

When X = 5.75
Ŷ = -86.171 + 41.251 X
Ŷ = -86.171 + ( 41.251 * 5.75 )
Ŷ = 151.02

Using excel -----> Data tab -----------> Data Analysis option -------> Regression