Sheet1
to RawData
and Sheet4
(the pivot table) to PivotTable
.SandersCompare
.PivotTable
to SandersCompare
. Make sure that the column headers are in row 1 and the row headers are in column A.
Warning: when you click on data in the pivot table, Excel may give you a fancy address involving GetPivotData(...)
; replace that with a normal address like PivotTable!B5
.
B2
. Write a formula (using cell references) that outputs the following:
B2
is a 1
if Akaka's vote on the first bill is the same as Sanders'sB2
is a -1
if Akaka's vote on the first bill is different from Sanders'sB2
is a 0
if either Akaka or Sanders did not voteFill > Right
. What will Sanders's row look like once we've filled it?SandersCompare
, add three columns on the right-hand side (use the COUNTIF
function):
num_agree
counts agreements with Sanders's votes.num_disagree
counts disagreements with Sanders's 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 values of this function?rank
.rank
. Look at the resulting data. Do you see anything surprising? Try to explain any surprises.