Activity 1-2

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 the Pivot Table 1 sheet to PivotTable.

In this task, you will compare each vote by each senator to Sen. Bernie Sanders' vote on the same bill.

1. Create a new sheet and name it SandersCompare (use the same file as in ACT 1-1).
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 Use formulas and fill handles to copy these. Note: If you do not have enough rows or columns to add the entire table, you can add multiple rows at once by highlighting the number of rows you need to add, then right-clicking. The "insert rows/columns" option will now insert as many rows/columns as you have highlighted.
3. Select B2. Write a formula (using cell references) that outputs the following:
   • B2 is 0 if either Alexander or Sanders did not vote
   • B2 is 0 if either Alexander or Sanders shows a blank cell for the vote.
   • B2 is 1 if Alexander's vote on the first bill is the same as Sanders'
   • B2 is -1 if Alexander's vote on the first bill is different from Sanders'
4. Can we change the order of the conditional tests in this formula and get the same results? Why or why not?
5. Do we need to worry about absolute references to specific rows or columns in this formula if we are going to use fill handles to compare Sanders to other senators? Why or why not? Remember the GradeSheet example from HW1-1.
6. Fill the first row using fill handles. What will Sanders' row look like once we've filled it? Will it be all 1's?
7. After the change is made, use the fill handles to fill the table. Verify your intuition about Sanders' row.

Finally, you will compute a score for each senator that represents how closely his or her voting record matches Bernie Sanders' record.

1. In SandersCompare, add three columns on the right-hand side, using the COUNTIF function to fill each one.
   • num_agree counts agreements with Sanders' votes.
   • num_disagree counts disagreements with Sanders' 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 possible values of this expression for any possible value of num_agree and num_disagree?
3. For readability, copy the row headings (i.e., the first column) to a column after rank using a formula/fill handle.
4. Select the entire table and sort in decreasing rank. An easy way to do this is to select the range to sort, click Data > Sort Range. Look at the resulting data. Do you see anything surprising? Try to explain any surprises.