Use excel in solving problem. I need help on how to use excel for the following
ID: 3370081 • Letter: U
Question
Use excel in solving problem. I need help on how to use excel for the following below.
A sample of 5 automobiles was taken, and the miles per gallon (MPG), horsepower, and total weight were recorded. Develop a linear regression model to predict MPG using horsepower.
A sample of 5 automobiles was taken, and the miles per gallon (MPG), horsepower, and total weight were recorded. Using the linear regression model developed, predict what the MPG is when Horsepower is 73 and when Horsepower is 68.
MPG Horsepower
44 67
44 50
40 62
37 69
37 66
A sample of 5 automobiles was taken, and the miles per gallon (MPG), horsepower, and total weight were recorded. Develop a linear regression model to predict MPG using total weight.
MPG Total Weight
44 1844
44 1998
40 1752
37 1980
37 1797
A sample of 5 automobiles was taken, and the miles per gallon (MPG), horsepower, and total weight were recorded. Using the linear regression equation you created, predict MPG when the total weight is 1987 and when the total weight is 2130.
MPG Total Weight
44 1844
44 1998
40 1752
37 1980
37 1797
MPG Horsepower 44 67 44 50 40 62 37 69 37 66Explanation / Answer
Q1 :
MPG Horsepower
44 67
44 50
40 62
37 69
37 66
We get regression output from excel : ( DATA tab > data analysis > regression )
Slope = b = -0.271 , intercept = a = 57.433
Hence the regression equation is
y = a + b*x
y = 57.433 - 0.271 * x
Here we have x = 73
y = 57.433 - 0.271 * 73
= 37.65 MPG
For x = 68
y = 57.433 - 0.271 * 68
= 39.01 MPG
Q2.
MPG Total Weight
44 1844
44 1998
40 1752
37 1980
37 1797
We get regression output from excel : ( DATA tab > data analysis > regression )
Slope = b = 0.006 , intercept = a = 29.207
Hence the regression equation is
y = a + b*x
y = 29.207 + 0.006 * x
Here we have x = 1987
y = 29.207 + 0.006 * 1987
= 41.129 MPG
For x = 2130
y = 29.207 + 0.006 * 2130
= 41.987 MPG
SUMMARY OUTPUT Regression Statistics Multiple R 0.587454 R Square 0.345102 Adjusted R Square 0.126803 Standard Error 3.277243 Observations 5 ANOVA df SS MS F Significance F Regression 1 16.97903 16.97903 1.580868 0.297611 Residual 3 32.22097 10.74032 Total 4 49.2 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Intercept 57.43328 13.62628 4.214891 0.024402 14.06838 100.7982 Horsepower -0.27123 0.21572 -1.25733 0.297611 -0.95775 0.415287Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.