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.

In this task, you will compare each vote by each legislator to Starry Lee's vote on the same bill.

1. Create a new sheet and name it StarryCompare.
2. Copy the column and row headers from PivotTable to StarryCompare. 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 (Note: be careful with your cell references. You will want to use absolute cell references ($) to reference Starry's row.):
   • B2 is 0 if either 何俊仁 Albert HO (if you did Task 3.3 in Activity 1 properly, and your Pivot Table is sorted by name, he should be in A2) or Starry did not vote on the first bill.
   • B2 is 1 if Albert's vote on the first bill is the same as Starry's
   • B2 is -1 if Albert's vote on the first bill is different from Starry's
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 Starry Lee to other legislators? Why or why not? Remember the GradeSheet example from HW1-1.
6. Fill the first row using fill handles. What will Starry's 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 from the previous question about Starry's row.

Finally, you will compute a score for each legislator that represents how closely his or her voting record matches Starry Lee's record.

1. In StarryCompare, add three columns on the right-hand side, using the COUNTIF function to fill each one.
   • num_agree counts agreements with Starry's votes.
   • num_disagree counts disagreements with Starry's votes.
   • num_not_voting counts the votes where either was absent.
2. Then add a column score that reports $$\frac{\text{num_agree}}{\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, the legislators' names) to a column after rank using a formula/fill handle.
4. Select the entire table and sort by decreasing score. An easy way to do this is to freeze the first row, then select the range to sort, click Data > Sort Range. Look at the resulting data. Do you see anything surprising? Try to explain any surprises.
5. Try other methods of scoring. For example, you can look only at the disagreements. How about if you use both agreements and disagreements? e.g. $$\frac{\text{num_agree}-\text{num_disagree}}{\text{num_agree}+\text{num_disagree}}$$ Does it make a difference?

1. Make a copy of ACT1-2_5 and save it to your Google Drive. Open it and go to the OffsetExample sheet.
2. Change the contents of cell B10 and notice that cells A11 and B11 automatically update to display the average of the homework number you specify. Verify that it works for a number of homeworks.
3. Look at the formula in cell B11. This is a combination of two functions, AVERAGE and OFFSET.

   First, notice how the functions are "nested" within each other (you are getting the average of the output of another function).

   Next, examine how the OFFSET function refers to cell B10 - 1. The value of the expression B10 - 1 is how many “steps” to the right to take from the starting cells (B2:B7) to get to the homework you're interested in.

   For example, if B10 is 9, then (B2:B7) is offset by 0 cells down and B10 - 1, or 8, cells to the right. This gets us all the scores for HW9, which is what we want as input for the AVERAGE function. Make sure you understand how this works before moving on.

4. Now suppose we want to enter a name in B13 (such as “Barry”) and then see the student's average for all homeworks in cell B15. First off, you need to determine what row is Barry's row. Write a formula (using the MATCH function) in cell B14 that identifies the row of the student who's name is in B13. Check that your formula works on several students' names.
5. The formula for cell B15 is similar to the formula in cell B11. Copy the formula in cell B11 into cell B15. Modify it to find the average of the student mentioned in cell B13. Remember that we found the student's row number for a reason.
6. What happens when you mistype a name in B13? To avoid the problem, we can have the user select a name from a list of possibilities. Go to the Data tab and select "Validation". Under criteria, click the grid icon and use the mouse to select the student names. Next, select List from a range and specify a data range of names that we want. Finally, save the validation. This option is listed under “Data Validation” because it verifies what kind of data can be put in the cell.