COMP1D04

From Scratch to Apps: Foundations of Computational Thinking and Literacy for Problem Solving

Grace Ngai

By the time you start this activity, you should have done the following.

- Follow the steps in Activity 1-1 to format the data.
- 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.

- Create a new sheet and name it
`StarryCompare`

. - 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. - 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

- Can we change the order of the conditional tests in this formula and get the same results? Why or why not?
- 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.
- 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?
- 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.

- 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.

- 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?`

- For readability, copy the row headings (i.e., the first column, the legislators' names) to a column after
`rank`

using a formula/fill handle. - 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. - 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?

`OFFSET()`

Function- Make a copy of
`ACT1-2_5`

and save it to your Google Drive. Open it and go to the`OffsetExample`

sheet. - 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. - 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. - 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. - 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. - 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.