For the European Parliament election in Great Britain  and Greater London Authority City wide list, a form of PR is used. Parties register lists of candidates, and voters vote for their preferred party. The counting is done using a method called the Dhondt method. This note describes the counting method and shows how an excel spreadsheet can produce the results.

The allocation of seats/winners is done in rounds and each time a seat is allocated the winning party’s vote is discounted. The discount factor is calculated as follows

Quota = Total Votes Cast / (No. of Seats won + 1)

Parties are ranked in order of the votes cast, and the first winner is declared, the party with the highest number of votes,  and their quota is adjusted. In the second round, the party with the maximum number of votes (as discounted) is declared to be the winner, and their votes are discounted.

Previously when looking at this, I came to the conclusion that the algorithm was too hard to code in Excel or SQL but I have now solved the problem for Excel.

This picture illustrates the contents of Cell Q37, which calculates the new discounted vote value aka the Quota. It contains the string,

=IF(P37,$B37/(SUM(D37,F37,H37,J37,L37,N37,P37)+1),O37)

If the party in question, determined by the row, won the last round i.e P37 = 1, then recalculate the quota, using the number of votes originally cast ($B37), divided by the number of seats previously won (derived by summing the seat winner columns which are 1/0 booleans) plus one. This means that the seat 7 quota calculation would not include P37 in the sum and seat 6 would not include either P37 nor N37 etc.

I have conditionally formatted each round’s seat winner with green text on a green background.

ooOOOoo

Here’s the pseudo code I sketched out to simplify the problem

seats = y

foreach partyname 
    	votes(partyname) = countvotes(partyname)
        quota(partyname) = votes(partyname)

foreach seat ( 1 … y)
        winner(seat) = nameof(max(quota))
        quota(nameof(max(quota))) = 
	        votes(winner(seat))/(countof(winner,winner(seat))) + 1)


3 Replies

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.