Question

The weight of a car can influence the mileage that the car can obtain. A random sample of 20 cars’ weights and mileage is collected. The table for the weight and mileage of the cars is given below. Use Excel to find the best fit linear regression equation, where weight is the explanatory variable. Round the slope and intercept to three decimal places.

Weight   Mileage
30.0   32.2
20.0   56.0
20.0   46.2
45.0   19.5
40.0   23.6
45.0   16.7
25.0   42.2
55.0   13.2
17.5   65.4
35.0   28.0
27.5   49.9
27.5   35.1
30.0   31.2
25.0   29.5
40.0   25.6
22.5   43.4
35.0   28.9
27.5   35.0
22.5   38.8
45.0   17.2

Answer 1

Solution:

X	Y	X^2	Y^2	XY
30	32.2	900	1036.84	966
20	56	400	3136	1120
20	46.2	400	2134.44	924
45	19.5	2025	380.25	877.5
40	23.6	1600	556.96	944
45	16.7	2025	278.89	751.5
25	42.2	625	1780.84	1055
55	13.2	3025	174.24	726
17.5	65.4	306.25	4277.16	1144.5
35	28	1225	784	980
27.5	49.9	756.25	2490.01	1372.25
27.5	35.1	756.25	1232.01	965.25
30	31.2	900	973.44	936
25	29.5	625	870.25	737.5
40	25.6	1600	655.36	1024
22.5	43.4	506.25	1883.56	976.5
35	28.9	1225	835.21	1011.5
27.5	35	756.25	1225	962.5
22.5	38.8	506.25	1505.44	873
45	17.2	2025	295.84	774
635	677.6	22187.5	26505.74	19121

Regression equation can be calculated as
Y = a + bx
Here a is Y intercept and b is slope of regression line
Slope of line can be calculated as = (n*Summation(XY) - Summation(X)*Summation(Y)/ n*Summation(X^2) - (Summation(X))^2)
= (20*19121 - 635*677.6)/(20*22187.5-635*635) = -1.1809
Intercept can be calculated as
Intercept = Summation(Y) - b*Summation(X)/n = 677.6 - 635*(-1.18)/20 = 71.3736

So regression line is
Y = 71.3736 - 1.1809*X