i have math and excel assignment

MTH 135 Online

Lesson 16 Handout – Line of Best Fit

Lesson Objectives:

1. Create a scatterplot for data in Excel.

2. Draw a reasonable line of best fit through data that is linear in nature using a

drawing tool in Excel.

3. Write the equation of the line of best fit and determine the domain and related

range for the data set. Find and describe the initial condition and average rate of

change for the line in terms of the data.

4. Reinitialize an independent variable to provide for more reasonable input values.

5. Use the equation of the line of best fit to interpolate and extrapolate new values

and determine the reasonableness of the new values.

For this lesson, please print off this handout and prepare to work in Excel with me.

Pause the video and write down steps you think you will need.

I plan to request that you do at least one homework problem in Excel and copy your

results into a Word document, then turn that document into a pdf file and send it to me

for a homework score.

EXAMPLE 1: Mean Heights of Kalama Children (Kalama, Egypt) From course textbook, section 4.10.

Please do the following:

1. Open the Excel file: Lesson 16 Data Sets (4.10).

2. Go to the first sheet which has data for the mean height of children from Kalama,

Egypt. Select the data values as well as the two rows of titles. Go to INSERT –

CHARTS and then find the icon for a scatterplot. Use the pull down menu on the

scatterplot to select a plot with points only:

3. Edit the graph as we did with histograms to add titles to both axes.

4. Go to INSERT – SHAPES and select a line tool.

5. Click to place a line anywhere on the scatterplot. After it is placed, you can

select the line and format thickness, color, and arrowheads on the ends. You

can also move each end of the line to place it where you think it should go.

6. Select two points that appear to be on your line of best fit. Use those two points

to write the equation of a line. Use Point-Slope Form to create the equation but

then solve the equation for height as a function of age.

7. Use the equation to identify the initial condition and ARC, rounding values to the

nearest 0.01.

Equation for h(A): _____________________________________

Initial condition: ______________ ARC: __________________

Meaning of initial condition and ARC:

8. Consider the interval in which the data was collected. Using that interval, find

each of the following:

Domain: _______________________

Range: ________________________

9. Use the COPY feature of Excel to copy your scatterplot and line of best fit:

EXAMPLE 2: Percentage of US Adults Who Smoke From course textbook, section 4.10.

Please do the following:

1. In the Lesson 16 Data Sets (4.10), go to the second sheet which has data for

the percent of US adults who smoke. Select the data values as well as the one

row of titles. Go to INSERT – CHARTS and then find the icon for a scatterplot.

Use the pull down menu on the scatterplot to select a plot with points only:

2. Edit the graph as we did with histograms to add titles to both axes.

3. Go to INSERT – SHAPES and select a line tool.

4. Click to place a line anywhere on the scatterplot. After it is placed, you can

select the line and format thickness, color, and arrowheads on the ends. You

can also move each end of the line to place it where you think it should go.

5. We are now going to REINITIALIZE THE INDEPENDENT VARIABLE. This is a

good idea because the years are large numbers that can be annoying to use and

the data points are all very far away from the vertical axis. To do this, start by

copying the entire table and pasting it below the first table. Delete all values in

the year column. Change the title to be: Years Since 1960.

6. The easiest way to adjust the input (independent) values is to subtract 1960 from

the previous. Set up a formula for the first where you start with the equal sign

and then click on the 1965 in the cell above and do -1960. Then copy down.

Check to make sure your values all make sense.

7. Select the data and the one row of titles and make another scatterplot. Position

the two scatterplots next to the appropriate data table. Again, place a line of best

fit in the data.

8. Select two points that appear to be on your line of best fit. Use those two points

to write the equation of a line. Use Point-Slope Form to create the equation but

then solve the equation for height as a function of age.

9. Use the equation to identify the initial condition and ARC, rounding values to the

nearest 0.01, where the percent of adults who smoke is given by P and the

number of years since 1960 is given by t.

Equation for P(t): _____________________________________

Initial condition: ______________ ARC: __________________

Meaning of initial condition and ARC:

10. Consider the interval in which the data was collected. Using that interval, find

each of the following:

Domain: _______________________

Range: ________________________

11. Now, let us use your equation to INTERPOLATE to estimate new values within

the domain of the given data. Find an estimate for the percentage of adults who

smoke for each of the following years. Then look at the graph to verify if the

estimate is acceptable.

1970: ______________

2003: ______________

12. Then use your equation to EXTRAPOLATE to estimate new values outside the

domain of the given data. Find an estimate for the percentage of adults who

smoke for each of the following years. Then look at the graph to verify if the

estimate is acceptable.

1960: ______________

1955: ______________

2010: ______________

13. Are your estimated values from extrapolating acceptable? Explain.

14. Do you think there is a limit to reasonable extrapolating with this data set?

Explain.

15. Use the COPY feature of Excel to copy both of your scatterplots and the line of

best fit for each. When you are finished, you can save your word file and Excel

file and then export the word file into a pdf to submit to your instructor

electronically.