ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
COUNTING ELECTIONS WITH STV: IMPROVED VERSION 5.0
ORIGINAL: Gribble19. UPDATED BY: A__C and Vennefly, MAYBE OTHER PEOPLE I FORGET ASK FOR CREDIT Changelog
2
ONLY CHANGE VALUES IN CELLS THAT ARE HIGHLIGHTED. YELLOW = CHANGE EVERY ROUND / ORANGE = CHANGE ONLY DURING SETUP
V.5.0 Borda Count preferences automatically sort out Senate winners (as marked in Overview) for Preferences 1-7. (A__C)
3
Setup: (Do this once)
V.5.0 Changed the Round Calculations threshold to be > instead of >= (in line with the electoral act) (A__C)
4
Values Tab
V.5.0 - Head-to-Head Comparison calculation via inputting 2 candidate names. (A__C)
5
Set S to be the number of Seats up for election
V.5.0 - Automatic Borda Count Import & Calculation from Candidates in Overview and pasted votes in Vote Table (A__C)
6
Set V to be the number of total votes cast in the election
V.5.0 - [STILL NEEDS TO BE VERIFIED FOR ACCURACY] Automated Round By Round Summary and First Preference Votes Table (A__C)
7
Overview Tab
V.5.0 - "Reason" column in Overview to assist automated Round By Round Summary. (A__C)
8
Add the list of candidates by pasting into "List of Candidates" column in alphabetical order.
V.4.0 - Borda Count column in Overview (A__C)
9
Set status of all candidates to active
V.4.0 - New Weight in Vote Table rounded to 5 decimal places (A__C)
10
Vote Table Tab
V.3.0 - Automatic Borda Count With Pasted Votes (A__C & Vennefly)
11
Add Preference columns until the amount of preference columns equals the amount of candidates running in the election.
V.3.0 - Automatic Calculation of New Active Preference (Vennefly)
12
Insert the votes into "Vote Table" In the columns Voter, Preference 1, Preference 2, etc.
V.3.0 - Automatic Transfer Value Calculation (Vennefly)
13
Set the "Weight" and "Active pref" to 1 for every voterWeight is the weight of that ballot. Active Pref denotes which preference of theirs is currently "active", in other words, the highest preference spot that doesn't contain someone that has already been either elected or eliminated.
V.3.0 - Highlighted Cells of where to edit (A__C)
14
Borda Count Tab
V.1.0 - Sheet Created (Gribble19)
15
Copy and paste the votes into the yellow highlighted cell in Borda Count to automatically calculate the values.
16
17
18
Counting Rounds: (Do this a lot of times, copy paste or reset the checklist as needed)
19
IMPORTANT: Whenever we copy paste something, we paste ONLY THE VALUES (SHIFT+CTRL+V / right click > paste special > only values). If you paste normally you are pasting formulas and everything breaks in the long run (or in the short run). This same copying only values should be done EVERYTIME you paste anything in this entire process.
20
Main Steps
21
Open the "Overview" tab. Copy and paste the "CURRENT VOTES" into the first empty round colum (the one for the current round).
22
From the "Overview". Copy the "List of Candidates" and "CURRENT VOTES" columns.
23
Open the "Round Calculations". Paste into "List of Candidates" and "Current Votes" the values you previously copied. The "Round Calculations" tab now shows who has and who hasn't reached the quota to be elected through the "YES"'s and "NO"'s
24
One or More YES'es? Do this! (If nobody is elected this round, move on to the next section)
25
In the "Overview" tab, set the 'Status' of all the people who reached the quota to be elected (all those with a YES in the "Round Calculations") from Active to Elected.
26
Open the "Vote Table" tab. Click on the calculator icon next to Votes. Click 'Group By CURRENTLY VOTING'.This shows all ballots in little groups based on who they are currently voting for. It's purely a visual thing to make the next steps easier.
27
Copy the "New Weight" values and paste into "Weight" (Remember to paste ONLY VALUES, not formulas).
28
Zero YES'es? Do this! (Do not do this section if you already did the One or More YES'es section in this round, in that case move on to the Final Steps section)
29
We have to determine who gets eliminated. Go to the "Overview" tab, on the calculator icon choose "Sorted by Votes, Active only"
30
The lowest ranked candidates will be on the bottom. If there is a tie, do the tiebreakers by checking their vote scores in every previous round sequentially, lowest score gets eliminated. In case it all ties, the candidate with the Borda count is eliminated.
31
Change the status of the eliminated candidate from Active to Eliminated
32
Final Steps
33
Open the "Vote Table" tab. Click on the calculator icon next to Votes. Click 'Group By CURRENTLY VOTING'.
34
Amend all the "Active Pref" numbers for all voters that are CURRENTLY VOTING an elected (green) or eliminated (red) candidates to the new active preference. You do this by copying the VALUES from the "New Active" column into the "Active Pref" column.
35
Copy and paste (only values) the entire "Round Calculations" table in the "Round Calculations Archive", so that it is possible to figure out what has gone wrong in a few days when we find out something has gone wrong.
36
And on to the next round, until all the seats are filled!
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100