By the time you start this activity, you should have done the following.
In this task, you will compare each vote by each legislator to Starry Lee's vote on the same bill.
StarryCompare
.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.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'sB2
is -1
if Albert's vote on the first bill is different from Starry'sFinally, you will compute a score for each legislator that represents how closely his or her voting record matches Starry Lee's record.
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.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?
rank
using a formula/fill handle.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.OFFSET()
FunctionACT1-2_5
and save it to your Google Drive. Open it and go to the OffsetExample
sheet.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.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.
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.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.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.