Question

Excel Lab 2: Regression and Goal Seek
In this lab, you will use Excel to determine the equation of the model which best fits a set of ordered pairs
obtained from data sets. You will enter data, graph the data, find the equation for the regression model,
and then use that equation to make predictions for the dependent variable. You will use the goal seek to make
predictions for the independent variable. Then you will consider how accurate your predictions are.
Part 1
The more a person weighs, the more Calories they burn every minute when they are walking. The following
table gives the number of Calories a person burns per minute of walking as a function of their weight in
pounds.

Weight (lbs)	100	120	150	170	200	220
Calories burned per minute	2.7	3.2	4.0	4.6	5.4	5.9

1. Enter the data into columns A and B on your spreadsheet, in rows 3-9.
2. Highlight your data and create a scatter plot (the first type, with no lines).
3. Make sure your graph includes a title and that both axes are labeled.
4. Right click on any data point and choose add trendline from the menu.
5. Select linear and check the boxes for display equation and show R2
(if you have Excel 2003 you will have to click on the options tab to see the last two check boxes).
6. Based on your equation from step 5, state the slope. Explain in a complete sentence what the slope
means in context of the problem. ( I need a specific answer.)
7. In the cell, C3 type the equation from step 5.
8. Type the equation in Excel format in cell C4 so that Excel calculates the number of Calories burned
per minute by a 100 lb person. Drag this formula to cell C9.
9. Compare the actual Calories burned in column B to the predicted calories burned in column C. How
accurate do you think your model is? ( I need a specific answer.)

@Please, show your all works and upload an Excel sheet.

Thanks.

Answer 1

Excel sheet: Values

Weight	Calories	y=0.027x-0.017	Error
100	2.7	2.683	0.017
120	3.2	3.223	-0.023
150	4	4.033	-0.033
170	4.6	4.573	0.027
200	5.4	5.383	0.017
220	5.9	5.923	-0.023

Formulae sheet

Weight	Calories	y=0.027x-0.017	Error
100	2.7	=0.027*A4-0.017	=B4-C4
120	3.2	=0.027*A5-0.017	=B5-C5
150	4	=0.027*A6-0.017	=B6-C6
170	4.6	=0.027*A7-0.017	=B7-C7
200	5.4	=0.027*A8-0.017	=B8-C8
220	5.9	=0.027*A9-0.017	=B9-C9

Since R2=0.9996=99%, we can say that model is 99% accurate.