How to Calculate RCV with Google Forms and Google Sheets
What is Ranked Choice Voting?
Ranked choice voting is a voting system where voters can select their most preferred choices on a ballot – first, second, third, and so on – instead of just voting for a single choice. If no candidate receives enough first ranked votes to win a majority, the lowest vote-getter is eliminated. Anyone who voted for the eliminated candidate has their vote redistributed to their next ranked choice.
This elimination and redistribution process repeats until a choice receives enough votes to win.
Why use Ranked Choice Voting?
Any time you're making a decision with more than two choices, ranked choice voting is the right tool for the job. It uncovers preferences with greater accuracy and fidelity than traditional voting methods.
Steps to Calculate RCV using Google Forms and Google Sheets
Depending on number of candidates and voters
Determine the decision that needs to be made
The first step is to get crisp on what decision needs to be made. Ranked choice voting works great in situations where you’re making a decision amongst a group of people with equal say. Groups like teams, organizations, and fan communities. This makes it differ from techniques like “weighted voting” or “100-point exercises.”
Fantastic uses for ranked choice voting include: selecting leadership, prioritizing work, running contests, and choosing award winners.
Once you know the decision that needs to be made, it’s time to start creating the form.
Create and configure the Google Form
If you don’t already have a Google account, you’ll need one. Then, go to Google Forms and select “Blank” under “Start a new form.”
Give your Form a name. This should be the decision you’re trying to make (e.g. “Favorite Fruits” in the example screenshot).
Change the question type to “Multiple choice grid.” This format most closely mimics the ranked choice ballots that are used for elections in New York City, Maine, and Alaska.
You’ll then need to make the Rows the choices people are ranking (e.g. “Strawberry”) and the Columns the ranking position (e.g. “1st”). This both more closely mimics the real-world ballots and helps with running the ranked choice calculations later on.
You can leave the “Question” field blank as it’s redundant with the title of the form. This also makes the eventual data in the spreadsheet a little bit easier to read.
Share the Google Form with your voters
You’ve now got a ranked choice form ready to go! Time to send it to your voters. Click the “Send” button in the upper right. Google allows you to send emails or copy and paste a link into whatever communication channel best works for you.
As voters fill out the form, you’ll see the results show in the “Responses” tab at the top of the page.
Get the Response Data into Google Sheets
Once all the votes are in, it’s spreadsheet calculation time! 🎉
Click on the “Responses” tab in your Google Form. In the upper right, there’s a green “Sheets” icon. Click it and then choose “Create a new spreadsheet.”
Your new Google Sheet should look something like this:
This example has 18 votes. Each row represents a voter’s ballot. Each column after Timestamp represents that voter’s rank for each candidate. Some cells are open, which is fine. This just means that the voter didn’t rank all the options on that ballot.
If all is looking well, duplicate the sheet (click the triangle at the bottom left of the screen where it says “Form Responses 1”). Call this new sheet “Round 1.”
It’s best to leave “Form Responses 1” untouched in case any mistakes are made later. This way it's always possible to get back to the original raw data.
Determine how many votes are needed to win
The point of any ranked choice voting round is to determine a winner. But, what does it take to win?
A core idea of ranked choice voting is that the winner should have majority support from the voters. For a single-winner election, that means 50%+1. To win this example election, the winner needs 10 votes (50% of 18 is 9. Plus 1 is 10).
Calculate Round 1
Now that all the votes are in and a winning threshold has been determined, we can start the calculations. Round 1 is relatively straightforward as it simply involves adding up all the first ranked choices each candidate received.
To make the spreadsheet able to be added up, you’ll do a “Find and replace” of the text “1st” and replace it with just the number “1.” Google Sheets adds up numeric cells in a column and will ignore any text-based cells. Pretty nice!
Click “Edit” > “Find and replace” to bring up the menu. Fill it out as shown in the image below. It’s very important to make sure the “Search” field is set to “This sheet” so that you don’t impact the original voting data. Then, hit the “Replace all” button. You should see that there were a number of instances of “1st” replaced. Click “Done” to finish.
The columns can now be added up. Clicking the column header will automatically display the Sum in the lower right corner. That’s a quick way to get a read on the vote totals for each candidate and if any of them managed to clear the winning threshold in Round 1.
However, chances are that didn’t happen. A more enduring solution is to create a cell with the vote totals at the bottom of each column. This way you can see the vote totals for all candidates at once. It will also come in handy as you get to later rounds.
The SUM function is your friend here. In your desired cell, type in =SUM(FirstCell:LastCell) to get the candidate's total. In the example, this would be =SUM(B2:B19) to get the total for the candidate named Strawberry. Then copy/paste or fill right to get the totals for the other candidates.
You’ve now totaled up the votes for Round 1.
If a candidate gets to a majority, you’re done. The ranked choice calculation doesn’t kick in because you’ve got a candidate with clear majority support. But, any time there’s more than two choices there’s a good chance you’re going to end up needing to perform ranked choice eliminations and vote redistributions.
Look at the totals for each candidate. Find the candidate with the lowest votes. Change the background color of that candidate’s column to a shade of gray. This is the candidate whose votes need to be redistributed at the start of the next round.
Duplicate the “Round 1” sheet and then rename the new sheet to “Round 2” at the bottom of your browser.
Here’s how things should look at the end of Round 1…
Calculate subsequent rounds (and find the winner)
The elimination and redistribution process below will be repeated for as many rounds as necessary until a winner is found.
This is the core of ranked choice voting. As candidates are found to be unviable, they are eliminated. But, people who voted for that candidate still have a shot at having their preferences reflected in the eventual result. No wasted votes. No penalty for voting for your favorite even if it wasn’t likely to win.
Back in the spreadsheet, you’ll redistribute the eliminated candidate’s votes to each voter’s next ranked choice. Here’s how…
First, replace every occurrence of “1” in the eliminated candidate’s column with “0.” Then, looking across the row for each “0,” find that voter’s next ranked choice. This will be the highest rank you see in any column that isn’t grayed out (because that denotes this candidate was eliminated). Replace the text in that cell with “1.” You have now successfully redistributed that vote.
Once you’ve redistributed all the votes, look at each candidate’s totals. If a candidate reaches the winning threshold, you’re done. If not, highlight the lowest vote-getter’s column. Duplicate the sheet. Rename the sheet for the next round. Repeat.
Here’s how the ranked choice calculation plays out for the example…
Example: Round 2
Papaya is the lowest vote-getter from Round 1. Its votes are redistributed to next ranked choices. No candidate has reached the winning threshold of 10 votes. So, we need to mark the lowest vote-getter from this round for redistribution in the next round (Watermelon - 3 votes).
Example: Round 3
Watermelon is the lowest vote-getter from Round 2. Its votes are redistributed to next ranked choices. No candidate has reached the winning threshold of 10 votes. So, we need to mark the lowest vote-getter from this round for redistribution in the next round (Apple - 4 votes).
Example: Round 4
Apple is the lowest vote-getter from Round 3. Its votes are redistributed to next ranked choices. Mango reaches the winning threshold of 10 votes. We’ve found our ranked choice winner!
Observations on the Example Election
If you’re squinting your eyes looking at the example data, you may notice a couple interesting things going on:
The leader in the first round did not end up being the ultimate winner…
It turns out that Strawberry had a strong initial showing, but no breadth of support beyond its first rank voters. In a typical “most votes wins” election, Strawberry would have won with just under 28% of the vote. With ranked choice voting, the underlying preference of the majority of voters for something other than Strawberry is revealed.
It started with 18 votes and ended with 15…
You can see in the initial data that some voters did not rank all five candidates. Some only ranked one. Then, that candidate was eliminated.
When a voter has no more ranked choices left, the vote becomes “exhausted” or “inactive” and is not included in later rounds. That’s why, as a voter, it’s always in your best interest to rank as many choices as you can.
FAQ
How long will this take me to calculate?
It depends on three main factors: the number of voters, the number of candidates, and whether tiebreakers are needed.
The above example was on the very simple end of the spectrum. Only 5 candidates, 18 total votes, and no tiebreakers. Following this guide, you’d likely spend between 20-30 minutes on it.
But what if you had 10 candidates? 100 votes? Encountered tiebreakers? Now you're talking about multiple hours of tedium.
To make that more tangible, with 10 candidates you’ll likely end up having 9 rounds of eliminations and redistributions. With 100 votes, you’ll be scrolling up and down the spreadsheet (and 10 candidates will also have you scrolling left and right). Lots of room for errors to occur. And, if there’s a need for tiebreakers to determine who gets eliminated, then that may be a whole other set of required calculations depending on how you choose to handle it.
Or…you could use RankedVote and have the results calculated instantly. 😉
How do I handle ties?
Ties can come up when determining the final winner or when determining who to eliminate in a round. There are simple methods that are quick, but don’t necessarily reflect voters’ preferences. There are other methods that reflect voters’ preferences, but take more time to calculate.
Simple
- Pick the First One – Quick and dirty. Gets you a result, but is biased to whichever candidate was placed first and is uncorrelated to voters' preferences.
- Flip a Coin – Removes the ordering bias, but is still uncorrelated with voters' preferences.
- Eliminate All at Once – In the event that multiple candidates are tied, but don't have as many votes combined as the next highest candidate, all can be eliminated in one batch.
Advanced
- Compare First Rank Votes – Go back to the Round 1 results and see if one of the candidates had more first rank votes. Underpinning this approach is the idea that a candidate with more first rank votes is more strongly preferred than the other candidate despite their current round vote totals being tied.
- Borda Count – This method looks across all votes received to see who was ranked highest. How to calculate Borda as well as variations on this approach can be found on Wikipedia.
What if no candidate reaches the winning threshold?
This can happen due to exhausted votes. In this scenario, the ranked choice winner is the candidate in the final round with the most votes. While this doesn’t quite get to the stated goal of majority support for the winning candidate, ranked choice voting is still uncovering the candidate with the broadest support amongst all options.
