math excel assignment

FOLLOW ALL DIRECTIONS CAREFULLY!!!!

Unless requested otherwise, round to the nearest 0.001.

PART ONE: Open the Excel File for each of the following. Complete the tasks for each problem. Copy your work from Excel and paste it in the indicated boxes in this document and type in requested information. Be sure to save your work as you work. You will then upload your saved work into Blackboard. (100 points possible)

1. Open the Excel File for this recitation and find the spreadsheet for Question 1 - Smoking. This data shows the percentage of adults who smoked cigarettes for various years since 1965. (30 points)

a) Select the columns for the Years Since 1965 and Adult Percentages and create a scatterplot. Enter appropriate titles for the chart and both axes.

b) Determine whether this data is best modeled by a linear, exponential or power function model by selecting the vertical and horizontal axes and changing the axes to logarithmic scales. Leave the plot in the best form: linear, semi-log, or log-log. Keeping in mind that you are working with REAL data, determine which of the three options gives the graph that is closest to being linear in nature. Move the circle to show the type of algebraic function that best models this data:

Linear Exponential Power

c) Now, select the Chart Elements and go to Trendline – More Options. Select the types of algebraic model that you want to use and then select the option to display the equation and the R2 value. Place those on the scatterplot so they can be read. Copy your scatterplot, showing the linear view of the data set, along with the equation and the scatterplot. Paste in the following box, making it fit so all features can be seen:

d) Identify the equation for your algebraic model. If it is in terms of e, please adjust the equation (showing your work) to see the actual growth factor and growth rate. (Note: Two of the three models will be fine as shown in Excel.)

e) The R2 value you get from Excel is the square of the correlation coefficient in our case. If the model is increasing, the cc is positive. If it is decreasing, the cc is negative. Take the square root of the R2 value you got in Excel and attach the appropriate sign (+ or -). Report the cc value for your model. Then tell whether you think your model is a strong, moderate or very rough predictor for this data set.

f) Use your model to predict the percentage of adults smoking cigarettes in 1950. Did you interpolate or extrapolate? Is your prediction reasonable?

2. Open the Excel File for this recitation and find the spreadsheet for Q2 – Planetary Motion. This data gives the average distance from the sun for each of seven planets and Pluto. These distances are given in Astronomical Units which is the average distance of Earth from the sun. For each of those seven planets and Pluto, the orbital time is given in number of Earth years. (30 points)

a) Select the columns of data and create a scatterplot. Enter titles for the chart and both axes.

b) Determine whether this data is best modeled by a linear, exponential or power function model by selecting the vertical and horizontal axes and changing the axes to logarithmic scales. Leave the plot in the best form: linear, semi-log, or log-log. Keeping in mind that you are working with REAL data, determine which of the three options gives the graph that is closest to being linear in nature. Circle the type of algebraic model you think is best:

Linear Exponential Power

c) Now, select the Chart Elements and go to Trendline – More Options. Select the types of algebraic model that you want to use and then select the option to display the equation and the R2 value. Place those on the scatterplot so they can be read. Copy your scatterplot, showing the linear view of the data set, along with the equation and the scatterplot. Paste in the following box, making it fit so all features can be seen:

d) Identify the equation for your algebraic model. If it is in terms of e, please adjust the equation (showing your work) to see the actual growth factor and growth rate. (Note: Two of the three models will be fine as shown in Excel.)

e) The R2 value you get from Excel is the square of the correlation coefficient in our case. If the model is increasing, the cc is positive. If it is decreasing, the cc is negative. Take the square root of the R2 value you got in Excel and attach the appropriate sign (+ or -). Report the cc value for your model. Then tell whether you think your model is a strong, moderate or very rough predictor for this data set.

f) Are you surprised by the cc you got for this data set? Why or why not?

3. Open the Excel File for this recitation and find the spreadsheet for Q3 – Toy Story. This data gives the domestic box office gross for the first ten weeks of the release of Toy Story 4 in June of 2019. The box office gross is given in millions of dollars. (30 points)

a) Select the columns of data and create a scatterplot. Enter titles for the chart and both axes.

b) Determine whether this data is best modeled by a linear, exponential or power function model by selecting the vertical and horizontal axes and changing the axes to logarithmic scales. Leave the plot in the best form: linear, semi-log, or log-log. Keeping in mind that you are working with REAL data, determine which of the three options gives the graph that is closest to being linear in nature. Circle the type of algebraic model you think is best:

Linear Exponential Power

c) Now, select the Chart Elements and go to Trendline – More Options. Select the types of algebraic model that you want to use and then select the option to display the equation and the R2 value. Place those on the scatterplot so they can be read. Copy your scatterplot, showing the linear view of the data set, along with the equation and the scatterplot. Paste in the following box, making it fit so all features can be seen:

d) Identify the equation for your algebraic model. If it is in terms of e, please adjust the equation (showing your work) to see the actual growth factor and growth rate. (Note: Two of the three models will be fine as shown in Excel.)

e) The R2 value you get from Excel is the square of the correlation coefficient in our case. If the model is increasing, the cc is positive. If it is decreasing, the cc is negative. Take the square root of the R2 value you got in Excel and attach the appropriate sign (+ or -). Report the cc value for your model. Then tell whether you think your model is a strong, moderate or very rough predictor for this data set.

f) For how many weeks past Week 10 do you think this model can be used to reasonably predict the box office gross? (There is NOT one correct answer for this! Just give an answer that seems reasonable to you.) Why do you think it is not as reasonable after that time?

4. For each graph, select the type of graph that you see and the model that would best fit the data. Slide the to the correct graph type and to the correct model. (10 points)

Graph type: Model Type:

□ Linear-Linear □ Exponential

□ Semi-Log □ Power

□ Log-Log □ Linear

Graph type: Model Type:

□ Linear-Linear □ Exponential

□ Semi-Log □ Power

□ Log-Log □ Linear

Graph type: Model Type:

□ Linear-Linear □ Exponential

□ Semi-Log □ Power

□ Log-Log □ Linear

Location for alternative answers if you cannot slide the x into the correct location.

a. Percent of US Adults

b. Infant Mortality Rates

c. Frequency of Use

Page 2 of 2

Page 2 of 2