Marriage and Divorce Rates: Excel

In this problem, you will investigate both marriage and divorce rates for the United States per 1,000 people in the population. You will also provide the results as a scatter chart.

1) Start Excel. Download and open the workbook named: Chapter_5-7_Marriage_Start
2) In cells D32:K46, insert a Scatter Chart to show the marriage and divorce rates over the years given. Select range A9:C21. On the Insert tab, click Recommended Charts, then open the All Charts tab, and then choose Scatter with the years on the horizontal axis and the rates per 1,000 on the vertical axis. Use cell references to B8 and C8 for Series names. Apply Style 1 on the Design tab. Add a chart title and choose the Above Chart option. Replace Chart Title with Marriage and Divorce Rates. Add a vertical axis title. Replace Axis Title for the vertical axis with rates per 1,000 people. Add the linear trendlines for both rates. To add a linear trendline to the data on the chart, select any point on the chart and right click on it. Select Add Trendline. In the Trendline Options window, select Linear with automatic trendline name and check "Display equation on chart". Drag it to any place on the chart so that it is more visible to read.
3) In cell G12, determine how much the divorce rate will decrease per century. Format cell G12 as Number with 2 decimal places.
Note: Enter a single value to answer the question.
4) In cell F18, determine the slope of the marriage trendline. Format cell F18 as Number with 2 decimal places.
Note: Type the slope value exactly as it appears in the equation on the chart.
5) In cell G18, determine the y-intercept for the marriage trendline. Format cell G18 as Number with 2 decimal places.
Note: Type the y-intercept value exactly as it appears in the equation on the chart.
6) In cell G19, calculate the marriage rate in 2050. Format cell G19 as Number with 2 decimal places.
Note: Use cell references for your calculations.
7) In cell F23, determine the slope of the divorce trendline. Format cell F23 as Number with 2 decimal places.
Note: Type the slope value exactly as it appears in the equation on the chart.
8) In cell G23, determine the y-intercept for the divorce trendline. Format cell G23 as Number with 2 decimal places.
Note: Type the y-intercept value exactly as it appears in the equation on the chart.
9) In cell G24, calculate the divorce rate in 2050. Format cell G24 as Number with 2 decimal places.
Note: Use cell references for your calculations.
10) In cell G27, determine whether it is true that it is not possible for the divorce rate to be greater than the marriage rate in a given year because only married people can get divorced.

The spreadsheet gives both marriage and divorce rates for the United States per 1,000 people in the population. To compute these, the National Vital Statistics System http://www.cdc.gov/nchs/nvss/marriage_divorce_tables.htm takes the number of marriages or divorces in a given year and divides by the total population, and then rescales to a rate per 1,000. The fact that the divorce rates are roughly half of the marriage rates leads to the incorrect misperception that this means half of all marriages end in divorce. Divorces in a given year could be anyone married before that year, while almost none of the people married in a given year will be divorced in that year.

a.) Create one scatterplot of both rates over the years given, insert trendline, and display equation. Interpret the divorce slope by filling in the blank in the following sentence: The divorce rate is decreasing by _________ divorces per 1,000 people per CENTURY. Answer should be a number to two decimal places. Include key. The axis label and the title should be chosen from the list:
a. Marriage and Divorce Rates
b. rates per 1,000 people

b.) Type in the slope and y-intercept for the marriage trendline exactly as they appear in the equation. Use the slope and y-intercept for the marriage trendline to predict the marriage rate in 2050. Format to 2 decimal places.

c.) Type in the slope and y-intercept for the divorce trendline exactly as they appear in the equation. Use the slope and y-intercept for the divorce trendline to predict the divorce rate in 2050. Format to 2 decimal places.

d.) True or false: It is not possible for the divorce rate to be greater than the marriage rate in a given year because only married people can get divorced.