We recently voted on the best web page and have voted on other topics on the past. Although the collection of votes has been aided by computer, the Google forms we used neither provided data validation nor calculated the winner. We don't need to do this by hand (or head), however. The can get the computer to do the number crunching if we're clever enough to tell it how. With a small vote like this one, it is not critical, but this same technique can be used for the next small vote or extended to take care of a large job.
The data from voting has been copied from a Google spreadsheet document to an Excel spreadsheet, VoteTally.xlsx, for you to download. The Google spreadsheet is the kind that is automatically generated for a form, in this case, the one used to collect your votes. What that form does not do is calculate the winner. That's what you will do with the help of Excel.
Note: Corrected instructions have been underlined.
Download the spreadsheet containing the data from the link above. So far only one worksheet has been populated and it has its default name. Rename the worksheet CleanedData, since it has removed entries that were deemed invalid, mostly votes made anonymously.
You might notice that some documents received votes for multiple places from a single voter. That was not the intention, even though the form did not actively verify this assumption. The election commission has decided to disallow these votes, but first they have to be detected. Excel can do this. In cells D1, E1, F1, and G1 record the column labels 1=2, 1=3, 2=3, and Disqualifiers.
In cell D2 use the equation =IF(A2=B2,1,0) to detect whether the votes in cells A2 and B2 are duplicates. Try to enter this formula with the help of the Formulas tab of the ribbon.
Similarly, enter =IF(A2=C2,1,0) into cell E2 to detect (and count) duplicates.
Lastly, detect double votes for second and third place with the formula =IF(B2=C2,1,0). Notice that there is not a good way to copy the formulas from cell to cell. Also, if you were keeping track of many different places, this strategy would become unwieldy and you would need to look for a better solution.
In the Disqualifiers column, sum the values in the three columns to the left. The formula is =SUM(D2:F2), but you will get further faster using some shortcuts.
The formulas you just entered apply to all votes, so select the range D2:G2 and then expand the selection downward across the remaining rows.
Name a second worksheet QualifiedData. All the data that will be counted in the vote will be transferred to this sheet. It won't be cut and pasted, but filtered.
Into cell A2 on the new sheet, enter =IF(CleanedData!$G2=0,CleanedData!A2,""). This looks at G2 on the CleanedData sheet and if the value is equal to 0, it puts the value of cell A2 on that sheet into cell A2 on the new sheet. Notice that the $ sign is used to pin some of the coordinates.
Select the new cell and stretch the selection horizontall to C2 and then stretch the new selection vertically to C12. The entire range is then A2:C12. You should see some data now and some blanks where votes have been disqualified.
It is now time to count votes. Label a third sheet Tally.
Starting at A2 and moving down, copy the names of the seven pairs as they were entered in the original Google form and appear on the other sheets. For example, put Noah Binkle & skjccodwk in A2, Jandro Frazo & Espada in A3, etc. You must have the spelling exactly the same. Therefore, use copy and paste.
Label columns B through E First, Second, Third, and Score. Results will to into these columns.
In B2 enter =COUNTIF(QualifiedData!A:A,$A2). This will count all entries in column A of the QualifiedData sheet which are equal to the value in A2 on this sheet. In other words, it counts the number of first place votes.
In C2 enter =COUNTIF(QualifiedData!B:B,$A2), which counts the second place votes.
In D2 enter =COUNTIF(QualifiedData!C:C,$A2) for the third place votes.
The votes needed to be added, but not added straight. First place votes are worth more than second and second more than first. The equation is similar to the one used to calculate your overtime and weekend pay. Use =3*B2+2*C2+D2 to give 3 points for first place, 2 points for second, and 1 for third.
Select the range B2:E2 and expand it downward to B2:E8 to calculate all scores.
Label A10 Totals.
Autosum B2:B10 so that the result appears in B10. This is a redundancy check.
Copy that autosum to the two cells to the right. Each column should have the same answer, since there should be the same number of first, second, and third place votes in this election.
The winner should be obvious from the score column, but we'll rearrange the rows to make it obvious. Select A1:E8, then find the Data tab of the ribbon. There should be a Sort button there. Press it and in the dialog that appears, Sort By Score and Order from Largest to Smallest. The winner should literally come out on top!
Save your spreadsheet with the name already given and email or USB it in. The election commission thanks you for ensuring that the tallying of votes was performed fairly and accurately.
Each instruction above is worth a point. In the printed report you see the points listed like this:
Rename the worksheet CleanedData.
Record the column labels 1=2, 1=3, 2=3, and Disqualifiers.
In cell D2 use the equation =IF(A2=B2,1,0).
Enter =IF(A2=C2,1,0) into cell E2.
Detect double votes with =IF(B2=C2,1,0).
Sum the values in the three columns to the left.
Select the range D2:G2 and then expand the selection downward.
Name a second worksheet QualifiedData.
Into cell A2 on the new sheet, enter =IF(CleanedData!$G2=0,CleanedData!A2,"").
Stretch the selection to A2:C12.
Label a third sheet Tally.
Copy the names of the seven pairs.
Label columns B through E First, Second, Third, and Score.
In B2 enter =COUNTIF(QualifiedData!A:A,$A2).
In C2 enter =COUNTIF(QualifiedData!B:B,$A2).
In D2 enter =COUNTIF(QualifiedData!C:C,$A2).
Use =3*B2+2*C2+D2.
Expand B2:E2 to B2:E8.
Label A10 Totals.
Autosum B2:B10.
Copy that autosum to the two cells to the right.
Sort By Score and Order from Largest to Smallest.