By the time you start this activity, you should have done the following:
In this task, you will compare each vote by each senator to Sen. Bernie Sanders' vote on the same bill.
SandersCompare
.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.B2
. Write a formula (using cell references) that outputs the following:
B2
is 0
if either Alexander or Sanders did not voteB2
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'Finally, you will compute a score for each senator that represents how closely his or her voting record matches Bernie Sanders' record.
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.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?
rank
using a formula/fill handle.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.