|
The data for this
activity can be found at the following website: http://lib.stat.cmu.edu/DASL/Datafiles/Birthrates.html
Reference: P.K. Whelpton and A. A. Campbell, "Fertility Tables
for Birth Charts of American Women," Vital Statistics Special Reports
51, no. 1. (Washington D.C.: Government Printing Office, 1960, years 1917-1975).
National Center for Health Statistics, Vital Statistics of the United
States Vol. 1, Natality (Washington D.C.: Government Printing Office,
yearly, 1958-1975.
Part 1: Numerical Analysis
of Birthrates
-
Open the web page listed above. Select and copy
the data on this page. Open Excel, select cell A1, and paste the data.
Select the Text to Column
option under the Data menu.
Follow the directions in the dialog boxes to format the columns so
that the years and birthrates are in two separate columns.
In step 2, be sure to select Tab and Space as the delimiters.
-
Let's reorganize the data so that the years are
in column 1 and birthrates are in column 2. Move the birthrates data
from column 1 to column 3 (Select column 1 and copy, then select
column 3 and paste). Delete
column 1 (Select column 1, then under Edit, choose Delete.)
-
Interpret the meaning of the values given for the
birth rates.
-
Examine the increase or decrease in birth rates
between consecutive years. To
make this task easier, we can create a formula and calculate the
differences in column C. Starting
in the row with 1918, calculate the difference between the birth rate
of 1918 and 1917 (Ex: =B3-B2). Use
the Fill down feature to
quickly calculate all the consecutive differences through 1975.
Interpret the positive and negative values.
-
Column C now contains the absolute differences
between consecutive years. In
column D, create a formula to calculate the percent increase or
decrease in birth rate between consecutive years.
What should you use as the numerator and denominator to
calculate the relative differences?
Be sure to display the values in column D as a percent. Use the
Fill down feature to
quickly calculate all the percent differences through 1975.
To display
a value as a percent:
- Click on the cell containing the value you wish to display as
a percent.
- Click on the
key on the toolbar. The decimal place of the current value should
move two places to the right and the percent symbol should be displayed.
|
Part
2: A Graphical View of the Birth Rates
To copy
an entire column of data onto another worksheet:
- Click on the gray column heading you wish to copy.
- Click on the copy button
on the tool bar.
- Select the desired tab on the lower scroll bar to
move to another sheet in the workbook.
- Once on the new sheet, click on the column
heading where you plan to paste the data.
- Click on the paste button
on the tool bar.
- (You can copy and paste more than one column at a time.)
SHORTCUT: To
copy an entire worksheet, under the Edit menu, choose Move or Copy
Sheet. Select the
Create a Copy option and indicate where you want the new sheet
located.
|
To rename
a worksheet:
-
Double-Click on the tab containing the current
name of the worksheet.
-
Type the new name for that sheet.
-
The new name should now appear on the tab on the
lower scroll bar.
|
To change
the scale on a graph’s axis:
- Double click on the graph to select it for editing (it will
have a gray outline).
- Double click on the axis you wish to change. The Format Axis
dialog box will appear.
- Click on the Scale tab and type in the desired values for the Minimum, Maximum,
and Major Unit and click OK.
|
In your analysis, recall that the birth rates are
per 10,000 twenty-three year old women.
Part
3:
Recent Trends in Birth Rates
-
While still on the “Data Graph” sheet, in
column C, starting at the row
with the 1970 data, enter the 20-24 year old data (attached) from
1970 through 1995. Be
sure to extend the year column down through 1995. (Hint: Use the Fill
Series option to extend the years.)
To create
a series using Fill:
- Select the cell containing the first value of the series
- Under the Edit menu, choose Fill, then Series…
- In the pop-up window, select whether the series
will be placed in a row or column. Also choose the type of
series (in this case choose linear).
- Enter the step value (how the series will change
from cell to cell) and the Stop value (the last entry in the
series). Click OK.
|
To add
data to an existing graph:
- Double click on the graph for editing.
- Under the Chart menu, select New Data...
- Select the cells containing the data you wish to add. (Be sure
to select the years and the birth rate values.)
- Click on OK. In the Paste Special dialog box, make sure that
New Series and Column are selected. Select the box
next to the statement Categories (X values) in First Column.
Click OK to add the new series of data. (The data series should appear in a different color with different
markings as data points.)
|
Extensions:
|
