POS 138 Quantitative Reasoning in Political Science

Home Up

HW4: Create a table in Excel, using Cross-National Data.
 [These instructions updated for Excel 2007]

Specific skills involved:

  • Finding OECD developed nation data
  • Selecting a relevant set of data from the downloaded dataset
  • Constructing a table:
    • using formulas to construct a measure
    • sorting data
    • merging cells
    • cell alignment and word wrap, centering data in columns, adjusting column widths
    • setting decimal places
    • using borders

Due:  We will do this in the lab (Schreoder 216) on Friday, 8/28

Note: Before doing this lab assignment, you should have read the JPDA, Chapter 2.

Goal: To create a table in Excel that looks like table 1, with these features:

  • Country-level data for two years.
  • Data sorted on the base year.
  • Formulas used to calculate the change in the third column.
  • Merged cells and text-wrap for the title.
  • Horizontal borders under the title and headings.
  • Source listed at the bottom of the table.

In this exercise, you should learn how to use the following Excel functions:

  • sorting data.
  • formulas (to calculate the average and net change).
  • copying and pasting formulas (so you don't have to retype them).
  • merging cells and wrapping text (the title and footnote for this chart is in four merged cells.
  • drawing lines in cell borders.
  • aligning text and setting decimals.

Instructions:

Find the Data: 

  • One of the best source of cross-national data for developed nations is the OECD.   Go to the SourceOECD website for an alphabetical index of data by topic.

  • EarthTrends Searchable database  allows you to select from a large number of variables on many topics, countries by category and years.  The data are downloadable as Excel files (before downloading select the "reverse years" option.

  • Also, Section 30 of the Statistical Abstract (that we used on the Data Search Assignment).
    (note this links to the Excel files for each table)

  • And the International section of the JPDA "Finding the Data" website

  • For some topics, you can try a google search, e.g.:
    international tax rates
    international murder rates
    international gun ownership rates,
     "by country" gun ownership rates

  •  other good data

    (but note that you may have difficulty getting the data into Excel)

Open the Excel file for that table (or paste the data into an Excel file). Note that in some of the Excel files there will be several worksheets of data, scan through these to select the best data.

  • Before you begin, make a copy of the worksheet (click on the tab at the bottom, Choose "move\copy" and "create a copy").
  • Also, save the file as an excel file (some of the file you download may be in other formats).
  • Begin by deleting all the unnecessary rows and columns.
  • Use formulas* to calculate net change or percentage change in the third column.
  • Adjust the column widths (use the mouse to drag the widths).
  • Be sure to use merge and wrap in the cell formatting:
    Highlight the cells to be merged and:
    use Format | Cells | Alignment -- check word wrap and merge to merge cells and wrap lines.
  • use Format | Cells | Border to draw lines.
  • To sort the data, highlight the entire set of rows and columns to be sorted, then select:
    Data | Sort
  • Change the fonts from Courier to something that looks nicer.

Notes:  It may be necessary (are useful) to delete smaller or poorer countries, countries that have missing data.  In general, a one-year change is not very meaningful.  A good table should have no blank cells.

Upload the Excel file as an attachment in a reply to appropriate message in the Webboard

Notes:

1. Formulas:

On the right, notice the formula in the formula bar for cell D7.

To create a formula in cell E4, enter:
        =C7-B7
Then copy that cell down the whole column (this can be done by dragging the little square on the bottom right hand side down the column).  Excel will automatically recalculate the formula for the entire column.

In some cases, you may need to calculate a percentage change rather than a net change.  For example:
        =(C7-B7)/B7*100

2. Merging Cells

The Cells A5 thru D5 have all been merged into a single cell using the merge and center button.

3. Aligning Columns

Unfortunately, centering the column of data does not properly align the decimal points of the column if the numbers have different numbers of digits.   Instead, try right-justifying the numbers and using the indent functions to center the column.

4. Decimals.

It may also be necessary to adjust the number of decimal places, particularly for the newly calculated "change" variable.  Highlight the cells, right click and choose "format cells"

Format Cells | Number (and specify the number of decimal places)

5. General formatting.

Various commands on the Font, Alignment and Number
portions of the "Home" ribbon will format the appropriate cells.

6.  Sort the data:

Highlight **ALL** the cells to be sorted and choose SORT from the Data Ribbon.

This is best done by highlighting the entire rows of data to be sorted.

 

Other good data: Cross-National Data Sources.

Several of these sites will have more data (more countries and more variables) than you need for this exercise. It is your responsibility to select meaningful data.

Abortion

·       Gutmacher report (choose two years) (see downloading data note above)

Voter Turnout:

·       Fair Vote (this has a good selection of countries, see downloading data note above)

·       International Institute for Democracy and Electoral Assistance  (turnout data)

Education Achievement 

·       TIMMS data

·       NCES, Education Indicators: An International Perspective (tables) (fewer countries, more data)

·       UNESCO

Women’s Representation

·       Global Database of Quotas for Women

·       Women in national parliaments

Crime and Punishment

·       BJS International statistics

·       Murder Rates (Handgun Murder rates)

·       Incarceration rates

·       Victimization survey (use recent year, replace commas with decimals points!)

Infant Mortality

·       March of Dimes website  (IM rates) (other statistics)

·       CDC data

Other International Data