Activity 1-2

February 5, 2013

Task 0: Starting Point

By the time you start this activity, you should have done the following.
  1. Follow the steps in Activity 1-1 to format the data.
  2. Use conditional formatting to make the background of any blank cell red. If you can't remember exactly how to do this, review the tutorial.
  3. Rename Sheet1 to RawData and Sheet4 (the pivot table) to PivotTable.
  4. Save your work (and save often!).

Task 1: Compare Votes to Bernie Sanders

  1. Rename an empty sheet SandersCompare.
  2. Copy the column and row headers from PivotTable to SandersCompare. Make sure that the column headers are in row 1 and the row headers are in column A.

    Warning: when you click on data in the pivot table, Excel may give you a fancy address involving GetPivotData(...); replace that with a normal address like PivotTable!B5.

  3. Select B2. Write a formula (using cell references) that outputs the following:
    • B2 is a 1 if Akaka's vote on the first bill is the same as Sanders's
    • B2 is a -1 if Akaka's vote on the first bill is different from Sanders's
    • B2 is a 0 if either Akaka or Sanders did not vote
  4. In an earlier task we were able to change the order of the conditional tests in an Excel function. Can we change the order here? Why or why not?
  5. Before we fill the rest of the table, there is one more change we have to make to this function. What is it? Remember the GradeSheet example from HW1-1.
  6. Fill the first row using Fill > Right. What will Sanders's row look like once we've filled it?
  7. After the change is made, use the Fill function to fill the table. Verify your intuition about Sanders's row.

Task 2: Rank each Senator

  1. In SandersCompare, add three columns on the right-hand side (use the COUNTIF function):
    • num_agree counts agreements with Sanders's votes.
    • num_disagree counts disagreements with Sanders's votes.
    • num_not_voting counts the votes where either senator was absent.
  2. Then add a column rank that reports $$\frac{\text{num_agree}-\text{num_disagree}}{\text{num_agree}+\text{num_disagree}}$$ What are the minimum and maximum values of this function?
  3. For readability, copy the row headings to a column after rank.
  4. Select the entire table and sort in decreasing rank. Look at the resulting data. Do you see anything surprising? Try to explain any surprises.