Download all Excel spreadsheets for this chapter.

Anyone who has used the recursive Borda count method to rank several candidates in an election knows what a tedious and computationally intensive job it is. Fortunately, the modern spreadsheet presents itself as an alternative to the laborious calculation involved in the mathematics of voting.

We start by considering the Math Appreciation Society (MAS) election having the preference schedule shown in Figure 1.1.

Figure 1.1: Preference schedule for the MAS election.

We first determine the winner of the MAS election by using the plurality method. In cell C9, we use the formula

=IF(C$3="A",C$1,0)

to count the number of first place votes for candidate A. We read this formula to mean "If what is in cell C3 is an "A", then put the value in cell C1 in this cell, otherwise put the value of 0 in this cell." The rationale for the absolute reference C$3 is that we then copy this formula into cells D9 through G9; in each case either a 0 or the number of appropriate first place votes will occur. It is then a simple matter to SUM these first place votes for candidate A in cell I9.

We repeat this process for candidate B in the next row changing the initial formula for cell C10 to =IF(C$3="B",C$1,0). Figure 1.2 shows the resulting spreadsheet after applying these formulas for all 4 candidates. Note that to save energy, the command =SUM(C9:G9) in cell I9 can be copied down column I into cells I10 through I12. To give more insight, the formulas used in this process are shown in Figure 1.3.

Figure 1.2: Using the plurality method for the MAS election.

Figure 1.3: Formulas used in applying the plurality method to the MAS election.

With a large number of candidates, we can also take advantage of the spreadsheet's SORT tool to determine a winner and at the same time do an extended plurality ranking of all the candidates. Such a sort is shown in Figure 1.4. In Microsoft Excel, one highlights the cells that need sorting and then chooses SORT under the Data menu.

Figure 1.4: Applying a sort and the plurality method to the MAS election.

Our next goal will be to determine the winner of the MAS election using a spreadsheet and the method of pairwise comparisons. Once again, recall the preference schedule shown in Figure 1.1. Our process will be to make a table containing all the information about each pairwise comparison. In the MAS election, there are clearly 3+2+1=6 pairwise comparisons as shown in Figure 1.5. Notice the orderly fashion in which we list all our possible comparisons.

In the row of our table detailing information on, say, B vs. C, and in any given column, we manually place either the formula for the cell location containing the number of voters in the column that prefer candidate B to C or we place a 0 in that cell. In the MAS election, for example, cell F12 has the formula =F1. In this way, the row 12 counts the number of voters which prefer candidate B to C.

Figure 1.5: Using the method of pairwise comparisons for the MAS election.

It is then a simple matter to sum the elements in row 12 of our table and check whether more than half, half, or less than half of the voters prefer candidate B to C. For this we compare the total number of voters which prefer B to C to the total number of voters located in the absolute cell location $I$2. Based on this comparison, we can use a nested IF-THEN-ELSE command in the spreadsheet to determine whether B or C would win in a head to head race. In our example, C would win as shown in cell I12. Formulas used in applying the method of pairwise comparisons on a spreadsheet are shown in Figure 1.6.

Figure 1.6: Formulas used in the method of pairwise comparisons for the MAS election.

It is now easy to do an extended ranking of the candidates in the MAS election using the output of Figure 1.5. Clearly C has 3 points, B has 2 points, D has 1 point, and A has 0 points. But what is it that is gained by doing these calculations on the spreadsheet as opposed to, for example, a calculator? Suppose that 2 preference votes were "lost" in the counting process and a recount needed to occur (recounts are quite common in close elections). Suppose these 2 ballots joined the 4 voters' ballots who listed the order BDCA as their preference. The beauty and power of the modern spreadsheet is that the results are automatically updated! Changing the '4' to a '6' in the original preference schedule gives the new results of a pairwise comparison election instantaneously (see Figure 1.7). Note that now candidate B is the winner and C takes second place.

Figure 1.7: Two more ballots found in the MAS election.

Lastly, we show how to implement the often tedious Borda count method to rank candidates in an election. Let us return to the preference schedule for the MAS election as given in Figure 1.1. The calculations required to rank the four candidates using the extended Borda count method are shown in Figure 1.8.

Figure 1.8: The Borda count method applied to the MAS election.

The formula

=IF(C$3=$I3,C$1*4,IF(C$4=$I3,C$1*3,IF(C$5=$I3,C$1*2,C$1*1)))

used in cell J3 looks complicated because of the use of nested IF-THEN-ELSE statements and various $ signs denoting absolute references. Try to understand how the formula assigns Borda points using your experience with the Borda count method as a guide. Basically, the formula is looking down column C to decide where candidate A is so as to assign the proper number of Borda points to candidate A.

Note that due to the strategic placement of '$' signs, we can copy the formula in cell J3 to all the cells in the box bounded by J3 and N6. The command =SUM(J3:N3) can then be used to compute the number of Borda points for candidate A. Copying this formula down column P will then calculate the number of Borda points for the other candidates.

Once again, note that any change in the preference schedule is automatically updated in the remainder of the spreadsheet! Figure 1.9 shows the formulas used to produce Figure 1.8. We note in passing that the SORT tool of the spreadsheet could be used to order the candidates according to their Borda points (See Figure 1.10).

Figure 1.9: Formulas used to produce Figure 1.8.

Figure 1.10: Using the SORT tool to order MAS candidates using Borda count.

Walking

Exercise 1.1 Design a spreadsheet that allows the user to input to one cell the number of candidates in an election and outputs to another cell the number of pairwise comparisons needed to do an extended pairwise comparison ranking of the candidates.

Exercise 1.2 Design a spreadsheet that allows the user to input to one cell the number of candidates in an election and outputs to another cell the number of pairwise comparisons needed to do a recursive pairwise comparison ranking of the candidates. Hint: This is a trick question!

Figure 1.11: The election for Exercise 1.3.

(a) Using a spreadsheet and the SUM command, determine the winner of this election using approval voting.
(b)Suppose that Bill had actually voted "yes" for candidate C. How do the results of this election change?
(c) Determine an extended ranking of the candidates in this election using the SORT tool of your spreadsheet.

Jogging

Exercise 1.4 Suppose that a new type of Borda count is used in the MAS election similar to that used in a famous golf tournament. That is, 4 points are given for a first place vote, 2 points for a second place vote, 0 points for a third place vote, and -1 points for a fourth place vote (these points correspond to eagle, birdie, par, bogey). With the aid of a spreadsheet, determine the winner of the MAS election using this voting method.

Exercise 1.5 [Excursions, Chapter 1, Exercise 17] An election with 5 candidates (A, B, C, D, and E), 21 voters, and the preference schedule given in Figure 1.12 is held.

Figure 1.12: The election for Exercise 1.5.

(a) With a spreadsheet, find the winner of the election using the plurality method.
(b) With a spreadsheet, rank the candidates using both the extended plurality method and the recursive plurality method.
(c) With a spreadsheet, rank the candidates using the extended pairwise comparisons method.
(d) Suppose 5 more voters entered the election with preference ballots of ABCDE. Does the outcome change if the method of pairwise comparisons is applied? If so, how?
(e) With a spreadsheet, rank the candidates using the extended Borda count method. Experiment with the spreadsheet to determine how one new voter in the election could vote so as to induce a tie. How could 2 new voters in the election vote so as to change the winner?

Exercise 1.6 An election with 5 candidates (A, B, C, D, and E), 100 voters, and the preference schedule given in Figure 1.13 is held.

Figure 1.13: The election for Exercise 1.6.

(a) With a spreadsheet, rank the candidates using the extended plurality method.
(b) With a spreadsheet, rank the candidates using the extended pairwise comparisons method.
(c) Suppose 5 voters with preference ballots of CDEAB switch their vote to BDECA. Does the outcome change if the method of pairwise comparisons is applied? If so, how? Was this calculation easy or difficult?
(d) With a spreadsheet, rank the candidates using the extended Borda count method. Experiment with the spreadsheet to determine how many additional voters would need to have preference ballots of CDEAB so as to induce a tie between C and D.

Exercise 1.7 Design a dynamic spreadsheet which allows the user to input into two cells the number C of candidates in the elections and the number N of voters and outputs to a third cell the smallest number of votes that a winning candidate can have. Hint: You may want to use the ROUNDUP command. To round a number such as 2.4 up to the nearest integer, use the formula =ROUNDUP(2.4,0).

Exercise 1.8 Design a spreadsheet which allows the user to input a positive integer N and outputs to another cell the sum

1+2+3+...+N.

Exercise 1.9 Design a spreadsheet which allows the user to input two positive integers M and N (with N>M) and outputs to a third cell the sum

M+(M+1)+(M+2)+...+M.

Exercise 1.10 [Excursions, Chapter 1, Exercise 6] The student body at Eureka High School is having an election for Homecoming Queen. The candidates are Alicia, Brandy, Cleo, and Dionne. The preference schedule for the election is shown in Figure 1.14.

Figure 1.14: The election for Exercise 1.10.

Suppose that the election rules are that when there is a candiate with a majority of the first-place votes, she is the winner. Otherwise, all candidates with 25% or less of the first place votes are eliminated and the ballots are recounted. Design a dynamic spreadsheet that determines which candidates are eliminated after the initial vote in this election.

Exercise 1.11 The 1999 preseason rankings for the eleven Big Ten college football teams are given in Figure 1.15 where the voters were various publications (e.g. SN = Sporting News, SI = Sports Illustrated).

Figure 1.15: 1999 preseason Big Ten college football rankings.

Implement the Borda count method on a spreadsheet to determine a composite ranking of these teams.

Running

Exercise 1.12 Design a spreadsheet that will allow a user to input the data for any election with three candidates A,B, and C and will output the number of Borda points each candidate received. Hint: Recall that there are 3!=6 possible preference ballots when three candidates are in an election.

Exercise 1.13 Design a spreadsheet that will allow a user to input the data for any election with three candidates A,B, and C and will output the number of first place votes each candidate receives.

Exercise 1.14 Design a spreadsheet that will allow a user to input the data for any election with three candidates A,B, and C and will output the resulting number of points for each candidate if pairwise comparisons are used to determine a winner. Hint: There are 3 comparisons to be made on 6 possible preference ballots.

Copyright © 1995-2008, Pearson Education, Inc., publishing as Pearson Prentice Hall Legal and Privacy Terms