Voting Excel Template and Add-In (Borda Counting and Schulze Method)
March 7, 2011 5 Comments
It is often necessary to find group opinion on a subject when making technical (or other) decision. Whether looking for group consensus, either a single option or prioritizing a set of conflicting candidates either human or technical in nature. I’ve had to sit through many of these electoral processes over the years, and can understand, having seen first-hand, just how lacking in rigor they often are. In order to get true buy-in, any ballot or ranking system must be seen to be fair, even by those who lose.
In an effort to improve scoring and voting systems, I’ve created a template spreadsheet, and Excel Add-In that allows two common election methods to be carried out in a consistent way.
The two voting systems I’ve chosen are the Borda Counting system, and the Schulze Method, the latter becoming the preferred ballot system for many public elections (Wikipedia, amongst a long list of others). First of all, let me briefly describe the two voting algorithms.
Borda Counting is a simple voting algorithm that is often used in the workplace to rank a variety of options, candidates or any other electoral system. It simply asks voters to order the list of candidates (candidates may take any form, people, products, or animals). Each ballot is tallied by giving the least preferred candidate a zero (0) score, and each subsequently preferred candidate in order, plus one (+1) from the previous candidate until all candidates are scored. For example if four candidates, A, B, C and D were voted on in a ballot A>B>C>D, then A would receive a score of three (3), B would get two (2), C get one (1) and D get zero (0). Every ballot would be tallied and summed to get a total score for each candidate. The winner is the candidate with the highest score.
Borda Counting’s strength is its simplicity, and its ability to bring to the forefront a winner that may not be the one voted most preferred the most often. This sounds weird, but if more people vote for a candidate in the higher positions, and vote the simple first-past-the-post winner last, then the other candidate has been voted preferred in the election as a whole. This makes ballots appear to represent the voters intention more clearly and properly considers voter preferences rather than just the most preferred candidate.
For example –
5 voters vote BACD, 1 voter ACDB, 2 voters CADB, and 1 voter DACB.
The Borda Counting results are: A>B>C>D. Surprised? 5 people voted for B being first. But this wasn’t enough to push out A which was voted for second place 4 times. B was voted in last place too often, and A was voted in first or second place in all cases. This is referred to as a Condorcet Paradox – big words meaning -“even though more people voted for a candidate in a pair-wise run-off, a candidate still loses!”
Before writing off Borda Counting, it is one of the better voting algorithms, and is easy for all voters to understand the math and logic; this isn’t always the case with other methods, lets consider the Schulze Method.
The Schulze Method
The Schulze Method takes an approach that the winner of an election should be the candidate that not only the most people want to win, but also the least people want that candidate to lose. It builds a pair-win (or pair-defeat) matrix of the number of time one candidate is preferred over another in all ballots. Unlike the Borda Counting algorithm, Schulze Method looks solely at a “win” when one candidate is preferred over another. Once this matrix is built, the relative strength between all candidates and paths to other candidates is built looking for the weakest value between all candidates and all paths, either directly or indirectly. For example, if A beats B, and B beats C, then A would indirectly beat candidate C. The winner is the candidate who has the strongest path win over all other candidates. The permutations grow as the candidate count grows, and for full details on the algorithm, the inventors article “A New Monotonic, Clone-Independent, Reversal Symmetric, and Condorcet-Consistent Single-Winner Election Method” and Wikipedia article offer pages of demonstration. Schulze Method’s weakness is its complexity; explaining it to a group of people, especially in a single sentence is hard – but it has undergone many years of mathematical scrutiny and offers one of the most tamper-proof (susceptible to tactical voting) options available.
The Schulze Method satisfies the criteria for not being susceptible to the Condorcet Paradox, and given our prior example in Borda Counting for example –
5 voters vote BACD, 1 voter ACDB, 2 voters CADB, and 1 voter DACB.
The result using the Schulze Method is B>A>C>D. I’ll leave it up to you to decide if that represents more voters choice that the Borda Counting result (which made A the winner, even though more people voted B as their most preferred). Before you choose Schulze Method universally, a Condorcet Paradox is easy to build when dealing with small numbers of voters, but rarely eventuated when the number of ballots grows substantially. I support both, and investigate the results if they don’t agree – which in practicality is rarer than this example might indicate.
Voting Systems Excel Add-In
I wrote this add-in to make it easier to run fair ballots, and to experiment with the different voting systems. The features of this template and add-in are –
- Supports Borda Counting where a total score for each candidate is arrived at by their position in a listed set of most-preferred to least preferred candidates
- Supports Schulze Method of voting where the most preferred candidate that wins a pair-wise election over every other candidate is the winner. This voting system is becoming a favored choice for many public elections, due to its resilience in the face of tactical voting which many other systems (like Borda Counting) can suffer.
- Support Tied candidates where the voter wishes to express no opinion of one candidate being better or worse than another.
- Support skipping candidates, where the voter expresses skipped candidates are less preferred than all candidates voted for, but no better or worse than each other.
- Provide a printable ballot template that explains the rules clearly to voters.
- Provide a set of Excel functions that help interpret and analyze the results.
The spreadsheet template allows ballots to be tallied in shorthand syntax (B more preferred than A, then D then C for the first votes). The most simple ballot case might be 5 ballots, split this way –
This would give results using both of the supported systems of –
Installing the Add-In
This add-in makes Excel User Defined Functions (UDF’s) available to the open excel spreadsheet. To use this add-in,
- Download the “Voting – GeekSpeakDecoded Ballot Template 1_0.zip” file and unzip it to a directory of your choice. There are numerous files in this zip file, and they all must be in the same folder. Download the template and add-in from here
- Open the spreadsheet “Voting – GeekSpeakDecoded Ballot Template.xlsx” file
- Open the add-in “VotingSystems.xll” file (seems strange doesn’t it, but it won’t close the current spreadsheet, just choose File-Open and navigate to the VotingSystems.xll file)
- Add-ins are a particular security risk for Excel, if you trust the add-in, enable Macro’s for the session by clicking “Enable Add-In for this session only”
What Is Included in the Spreadsheet Template?
The spreadsheet template has four main pages. These worksheets are –
Examples – Shows each of the functions added by this add-in in operation. It also shows how to get the resulting ranking using both of the supported voting systems, and how to analyze in more detail the results.
Your Ballot – A blank worksheet ready to enter the results for a ballot of your own.
Printable Ballot Sheets – A template for creating your own paper ballots. This template contains the rules for proper voting using this spreadsheet and the supported skipping and tied value features.
Reference – More detail on the algorithms used, and detail on each function available in the VotingSystems.xll add-in.
The examples and reference worksheets form the bulk of the documentation required, and by experimenting with the Your Ballot worksheet, it is easy to get up and running.
For more advanced worksheets, the custom functions added can be used for building your own template in a format of your choosing. The custom Excel functions added when this add-in is installed are:
|BordaRankings||Given a list of ballots, returns the resulting preference order using Borda Counting.|
|SchulzeRankings||Given a list of ballots, returns the resulting preference order using Schulze Method for voting.|
|IsBallotValid||Returns True (for a valid vote) or False (given an invalid vote) given a ballot vote string.|
|InvalidBallotReason||Returns the reason a ballot vote string is invalid.|
|BordaCandidateScore||Returns the individual candidate score for a set of ballots in a Borda Counting election.|
|BordaCandidateRank||Returns the individual candidate rankingfor a set of ballots in a Borda Counting election.|
|SchulzeCandidateRank||Returns the individual candidate rankingfor a set of ballots in a Schulze election.|
|PairWinScore||Returns the number of wins from one candidate over another in a Schulze election (or Borda Counting election, but pair wins play no part in the Borda Count result).|
|PathStrengthScore||Returns the path strength from one candidate over another in a Schulze election. See http://en.wikipedia.org/wiki/Schulze_method|
|RankOrderCentroidValueBorda||Retuns the ROC for a given candidates final ranking in a Borda Counting election. ROC’s are used to distribute weights that sum to 1 when for decision support (e.g. when choosing a software package)|
|RankOrderCentroidValueSchulze||Retuns the ROC for a given candidates final ranking in a Schulze election. ROC’s are used to distribute weights that sum to 1 when for decision support (e.g. when choosing a software package)|
I’ll be doing future work on this add-in and future articles will cover the algorithms and the pro’s and con’s of each voting systems (when to choose one over another), and this spreadsheet will be the tool used to examine the voting results. Please comment on how to improve the experience of this template and share your thoughts on how you used it in your business and technical decisions.