You Don’t Need An App For That: A Google Sheets Guide to Calculating The Nevada Caucuses
A failed vote tallying app. Incorrect, missing, and duplicate precinct results. A horrendous party response to these failures. By all accounts, the 2020 Democratic Iowa Caucuses were a catastrophe. A part of that failure was the complex formula used to allocate delegates in the caucuses compounded with the requirement to report more data than in years past.
And Nevada is in position to be in a similar disaster. Nevada uses similar formulas to allocate delegates from its caucuses. After scrapping their plans to use a similar app to the one used in Iowa, they decided on a “tool” installed on iPads — which suspiciously sounds exactly like an app — to help the volunteers navigate the necessary calculations.
But, such an app is not necessary. In fact, it is possible to use Google Sheets to calculate the delegates for the Nevada caucuses, an idea that others besides me have suggested. In fact, the Nevada Democratic Party itself has used Excel in the past to compile results they have gotten by phone. And so let us go thorough the steps to making such a spreadsheet.
Calculating Total Number of Delegates In a Precinct
First, we will create the “Precinct Master Sheet”. This is a list of all the caucus precincts and total delegates for each one. Fortunately, the Nevada Democrats have already provided a list on their website. Manually inputting the location and delegate count for almost 2,000 precincts would be very cumbersome, so I used a tool called Tabula to extract the data from the PDF and import it into Google Sheets.
It would be prudent to validate this data to make sure it is consistent with the Nevada rules for allocating delegates. For these rules, we consult the Precinct Delegate Apportionment Caucus Memo, which describes how to allocate total delegates for each precinct.
With the information in these two pages, we can create a column in our Google Sheets page for the number of delegates for a precinct given the number of registered voters in the county and the number of registered voters in the precinct. The number of registered voters for each Nevada county can be found on the Nevada Secretary of State website. While it is possible to use the built-in Google Sheets functions to create the formula for each cell, it is cleaner to create our own custom function.
A useful feature to include in our spreadsheet would be highlighting if our calculated delegate count for a precinct differs from the one given by the Nevada Democrats. To accomplish this, we will use conditional formatting to turn a cell in our column red if the number in the calculated column is different than the number in the given column for each row.
Now we can complete our columns for validating the given delegate count. To calculate the number of alternates, simply divide the number of delegates by 2, rounded to the nearest whole number.
The above validates every Nevada precinct except for the at-large strip caucuses. Those caucuses use an entirely different formula to determine delegate count, which are described on the Strip Caucus Eligibility and Delegate Allocation Memo. Unfortunately, part of the formula to validate the strip caucuses is the 2016 share of strip caucus participation. As I cannot find this information, I cannot validate the delegate count for the sites; therefore, I’ll assume that the information given to us is correct and fill the calculated columns the same as the given columns.
As there are no red cells, all the delegates to be allocated from each precinct site have been validated.
Making Caucus Site Worksheet Template
Now we will create the “Caucus Template” sheet. This sheet will be what we use to calculate the delegates that each candidate should receive at a certain caucus.
First, we need an area to put the county and precinct number or area if it’s a strip caucus. Then, using the following formula, we can refer to the Precinct Master Sheet to get the number of delegates for any site.
=INDEX(FILTER(all_precinct_delegates, counties=county, precincts=precinct),1,1)In simpler terms, this formula finds the rows of the Precinct Master Sheet that match the county and precinct we put in. Then we get the first cell of the resulting array. Note that to clarify the intent of the formula, we have named the ranges and cells used. The cell for this formula will be called “site_delegates”.
Later we’ll add the viability threshold to this area, but first let us set up the main area for input. That area, shown below, is where we’ll input the candidates and the number of preference votes they received in the first and final alignment.
Not only is there an area for inputting candidate information, but there is also an area that adds up all totals of each column. The total in column B is important in determining the number of delegates that each candidate gets. In the rest of this article, this total will be called “total_voters”. Notice also that some of the candidate rows are gray. The candidate rows have conditional formatting such that they turn gray if there is no candidate name for that row, which was done to better focus attention to the rows that are in use.
Now that we’ve created this area, we have all the components needed to add the viability threshold to the other area. The rules for the viability threshold are outlined on page 10 of the delegate plan:
From here, making a formula for viability is simple. However, there is one scenario we still have to consider: what if no candidate reaches the initial viability threshold? While very unlikely, this scenario is technically possible and the delegate plan has accounted for this scenario, so our formula should too. The delegate selection plan for this is as follows:
Once again, we save the final formula for viability as a custom function. The cell that we calculate the viability threshold into is named “viability”.
Now that we can find the viability threshold, we can calculate the rest of the steps needed to allocate delegates, which are laid out in the Delegate Count Scenario Caucus Memo from the Nevada Democrats website.
For our spreadsheet, we create columns for the result of the caucus formula, the rounded delegates, the tiebreaker cards, and the final delegate count. To fill in the caucus formula, we need the first alignment total, the number of delegates for a site, and the final alignment column. Then we can fill the caucus formula column with the following formula, where D2 is the final number of votes a candidate received.
=D2/total_voters * site_delegatesAfter rounding the caucus formula column and putting the result in the next column, we turn to the tiebreaker column. Per the delegate plan, the tiebreaker scenario is a deck of cards, so the column is to input cards in the form of “[rank] of [suit]” (e.g. 10 of spades).
And now we can create a function that calculates the final delegate count for all the candidates. We create helper functions to help us sort the tiebreaker cards.
The above function is the one we use if the precinct has more than one delegate to allocate. Otherwise the process is much different. Instead of using a caucus formula, we use the final preference count, and the candidate with the most votes wins the delegate. We use the tiebreaker if there is a tie for the top candidate. This is expressed with the following custom function:
And so we have created all of the possible columns needed to calculate a Nevada caucus delegate result. We include other conditional formatting to columns and cells so that it is visually easy different information such as incorrect or missing information, which candidates fail the viability threshold, and whether all of the delegates have been allocated to the correct candidate.
Now that we have completed our worksheet, let us calculate a Nevada caucus precinct example. In this example, there are 8 delegates in the precinct and 4 viable candidates:
We assume that the “People in Preference Group” column reflects both the initial and final group preference. Putting in the candidate name and voter count, we see that this example calculates the correct distribution of delegates. Don’t forget to pick a county and a precinct that has the same number of delegates as the example. In our case, we used Carson City Precinct 103.
Other sample precincts can be found on the Nevada Democrats website.
To get a copy of the complete Google Sheets spreadsheet on your Google Docs, click here.
Due to the confusion if multiple people were editing the master source at the same time and to prevent undue changes to the master copy, this link makes a local copy.
The Github repository of the functions used for the spreadsheet can be found here.
