- TNP Nation
- PotatoFarmers
- Discord
- Freg#0420
Following the passing of the Lessening Repeating Work (L.R.W) Bill, this Justice election has been a headache with regards to how we are counting the votes. Being one of the election supervisors, I can't help but imagine what Sil Dorsett and I have to do after voting closes, opening spreadsheets after spreadsheets and constantly having to refer to the posts to find out who is everyone's next choice on the list.
So I decided to figure out how to reduce the effort. I know Eluvatar has been the person to look for with regards to spreadsheets that does the election vote counting, but I have decide to take matters into my own hands, and attempt to create a new spreadsheet on my own. For me, it wasn't exactly "from scratch" as you may imagine. I had previously done a spreadsheet to do a single transferable vote for 1 winner, similar to that of how the Delegate/VD/Speaker elections work. So I attempted to convert that into something which I hope current and future election supervisors can work with more easily, and I present the following.
1. Executive/Legislative Election Spreadsheet - Link
The spreadsheet above is supposed to be a template, but I have keyed in the results of the May election just to see whether it works as planned. Having hidden none of the spreadsheets, there are a total of 10 of them but in reality, only 4 will be edited by the respective supervisors - Gen Candidates, Gen Votes, Gen VoteCount and Voter Roll. I give an overview of what should be edited and my rationale for the design (if any):
Gen VoteCount - Trying to imitate what the original VoteCount sheet looks like, I decided to present the results for all 3 roles on in 1 sheet, with the listing of how votes are counted round by round for up to 5 rounds. If you look at other spreadsheets, you would see that I have done them individually in the "Delegate", "Vice-Delegate" and "Speaker" sheets respectively, but I thought a familiar presentation style might be better.
Gen Candidates - A lazy way for someone to key in candidate lists. Maybe I could instead just base it off the list given in Gen VoteCount, but since my original spreadsheet didn't have the equivalent of Gen VoteCount, I just left it as such. 10 candidates are supported for the spreadsheet, so it allows the listing of up to 10 candidates.
Gen Votes - Not every column needs to be edited, only the ones where I coloured the header grey. I tried to mirror it to be similar to the original sheet, and chose to allow for the listing of options vertically downwards instead of horizontally. This would mean some duplication (in which we have to key in voters' name 6 times), but allows for the votes to be transposed to the respective working spreadsheet. I have chosen to squeeze private voter IDs with public voter usernames in 1 column, with the second column only a way to check if the voter is a valid voter. There is conditional formatting to detect invalid voters, such as vote 59 and vote 64, and the supervisors will know that they should not enter anything else anymore, leaving the row blank (just like before).
Voter Roll - This is the only sheet which is exactly the same. Enter the voter list here and it would help in detecting invalid voters.
The remaining sheets of the format "Role (Tabluating)" and "Role" are working sheets where the runoff is being calculated. In reality, they could be hidden, but I have decided to open up everything for now.
2. Judicial Election Spreadsheet - Link
This one was created really last minute, making as little changes as possible from the Gen Election Spreadsheet, in order to make my life easier. The spreadsheet given would be what I would present for July 2020 Judicial Elections if the sheet is approved, so literally every bit of data is inside. Again, I take my time to explain what does every sheet do:
Jud Candidates - Same as above, place where candidate list is entered.
Jud Votes - Similar to Gen Votes, but the choices are listed vertically, so the voter's name is only entered once. It supports 9 choices, but 5th to 9th choice is hidden by me for the sake of this election.
Jud Results - I don't know how I should present this in a format similar to Gen VoteCount, so I decided to use whatever format I had and left it as such. It is a work in progress, considering how little time I had to come up with this (I started when LRW Bill passed, btw), so I do want to seek everyone's opinion on how this should be presented in a better manner.
Voter Roll - Title explains it all. Used in detecting invalid votes.
The sharing settings are shared to allow commenting for each and every member of the EC, based on the EC nominal roll. I do hope to seek everyone's comments on my proposed template spreadsheet, since everyone would be the ones using it when it is their turn to supervise an election. I will also answer any queries about the sheet, and if anyone spots mistakes/bugs/errors, you can point them out to me. Thanks!
So I decided to figure out how to reduce the effort. I know Eluvatar has been the person to look for with regards to spreadsheets that does the election vote counting, but I have decide to take matters into my own hands, and attempt to create a new spreadsheet on my own. For me, it wasn't exactly "from scratch" as you may imagine. I had previously done a spreadsheet to do a single transferable vote for 1 winner, similar to that of how the Delegate/VD/Speaker elections work. So I attempted to convert that into something which I hope current and future election supervisors can work with more easily, and I present the following.
1. Executive/Legislative Election Spreadsheet - Link
The spreadsheet above is supposed to be a template, but I have keyed in the results of the May election just to see whether it works as planned. Having hidden none of the spreadsheets, there are a total of 10 of them but in reality, only 4 will be edited by the respective supervisors - Gen Candidates, Gen Votes, Gen VoteCount and Voter Roll. I give an overview of what should be edited and my rationale for the design (if any):
Gen VoteCount - Trying to imitate what the original VoteCount sheet looks like, I decided to present the results for all 3 roles on in 1 sheet, with the listing of how votes are counted round by round for up to 5 rounds. If you look at other spreadsheets, you would see that I have done them individually in the "Delegate", "Vice-Delegate" and "Speaker" sheets respectively, but I thought a familiar presentation style might be better.
Gen Candidates - A lazy way for someone to key in candidate lists. Maybe I could instead just base it off the list given in Gen VoteCount, but since my original spreadsheet didn't have the equivalent of Gen VoteCount, I just left it as such. 10 candidates are supported for the spreadsheet, so it allows the listing of up to 10 candidates.
Gen Votes - Not every column needs to be edited, only the ones where I coloured the header grey. I tried to mirror it to be similar to the original sheet, and chose to allow for the listing of options vertically downwards instead of horizontally. This would mean some duplication (in which we have to key in voters' name 6 times), but allows for the votes to be transposed to the respective working spreadsheet. I have chosen to squeeze private voter IDs with public voter usernames in 1 column, with the second column only a way to check if the voter is a valid voter. There is conditional formatting to detect invalid voters, such as vote 59 and vote 64, and the supervisors will know that they should not enter anything else anymore, leaving the row blank (just like before).
Voter Roll - This is the only sheet which is exactly the same. Enter the voter list here and it would help in detecting invalid voters.
The remaining sheets of the format "Role (Tabluating)" and "Role" are working sheets where the runoff is being calculated. In reality, they could be hidden, but I have decided to open up everything for now.
2. Judicial Election Spreadsheet - Link
This one was created really last minute, making as little changes as possible from the Gen Election Spreadsheet, in order to make my life easier. The spreadsheet given would be what I would present for July 2020 Judicial Elections if the sheet is approved, so literally every bit of data is inside. Again, I take my time to explain what does every sheet do:
Jud Candidates - Same as above, place where candidate list is entered.
Jud Votes - Similar to Gen Votes, but the choices are listed vertically, so the voter's name is only entered once. It supports 9 choices, but 5th to 9th choice is hidden by me for the sake of this election.
Jud Results - I don't know how I should present this in a format similar to Gen VoteCount, so I decided to use whatever format I had and left it as such. It is a work in progress, considering how little time I had to come up with this (I started when LRW Bill passed, btw), so I do want to seek everyone's opinion on how this should be presented in a better manner.
Voter Roll - Title explains it all. Used in detecting invalid votes.
The sharing settings are shared to allow commenting for each and every member of the EC, based on the EC nominal roll. I do hope to seek everyone's comments on my proposed template spreadsheet, since everyone would be the ones using it when it is their turn to supervise an election. I will also answer any queries about the sheet, and if anyone spots mistakes/bugs/errors, you can point them out to me. Thanks!