TWG Vote Tracker - Google Spreadsheet

The last place you'll ever drink away the day...again.
User avatar
necklessone
Post Strumpet
Post Strumpet
Posts: 4639
Joined: 25 Jul 2013, 16:13:44
TWG 1 Posts: 0
Location: Somerville, NJ (US Eastern Time Zone)

TWG Vote Tracker - Google Spreadsheet

Postby necklessone » 04 Sep 2013, 17:25:42

Alright, so here's the new vote tracker I've been working on.

Just copy a player list into column A and H and then start filling votes into columns D:F. The vote count will update itself (though you need to sort it with J as the key column in descending order) and the sheet will flag who has voted and which player has votes that need retraction. A vote history in UBB is in column N.

This tracker will not tell you when ICB has made an unexpected vote change.

User avatar
Iron Clad Burrito
Propmaster Emiritus
Posts: 4191
Joined: 13 May 2013, 18:27:41
TWG 1 Posts: 0
Location: Tennessee (US Central Time)

Re: TWG Vote Tracker - Google Spreadsheet

Postby Iron Clad Burrito » 04 Sep 2013, 17:33:57

necklessone wrote:Alright, so here's the new vote tracker I've been working on.

Just copy a player list into column A and H and then start filling votes into columns D:F. The vote count will update itself (though you need to sort it with J as the key column in descending order) and the sheet will flag who has voted and which player has votes that need retraction. A vote history in UBB is in column N.

This tracker will not tell you when ICB has made an unexpected vote change.



Next time I'll PM you in triplicate 24 hours beforehand. :lol: :lol: :lol:

User avatar
necklessone
Post Strumpet
Post Strumpet
Posts: 4639
Joined: 25 Jul 2013, 16:13:44
TWG 1 Posts: 0
Location: Somerville, NJ (US Eastern Time Zone)

Re: TWG Vote Tracker - Google Spreadsheet

Postby necklessone » 05 Sep 2013, 06:53:13

I've made a quick update this morning. Now the only data you need to enter is the player list in column A and the votes in D-F. The spreadsheet will handle all of the vote tabulation and automatically sort the count (Raw count data in P:S, sorted in H:L).

User avatar
necklessone
Post Strumpet
Post Strumpet
Posts: 4639
Joined: 25 Jul 2013, 16:13:44
TWG 1 Posts: 0
Location: Somerville, NJ (US Eastern Time Zone)

Re: TWG Vote Tracker - Google Spreadsheet

Postby necklessone » 06 Sep 2013, 07:19:31

Working on a few small changes (cleaner appearance of blank spreadsheet, automatic list of players who haven't voted).

User avatar
necklessone
Post Strumpet
Post Strumpet
Posts: 4639
Joined: 25 Jul 2013, 16:13:44
TWG 1 Posts: 0
Location: Somerville, NJ (US Eastern Time Zone)

Re: TWG Vote Tracker - Google Spreadsheet

Postby necklessone » 10 Sep 2013, 05:48:14

A few more changes as of this morning.
  • A column for vote value (useful for negative votes or multiplied votes); if it's left blank the spreadsheet assumes the vote is worth 1.
  • Validation on the vote inputs, mainly for the handy drop down list as you type. It just flags inputs that aren't valid players while still allowing them.
  • Switch to colons as spacers since they work much better with negative votes.

User avatar
FurinMirado
Post Harlot
Post Harlot
Posts: 2530
Joined: 25 Jul 2013, 16:55:18
TWG 1 Posts: 0
Location: Indianapolis, IN - Eastern Time Zone

Re: TWG Vote Tracker - Google Spreadsheet

Postby FurinMirado » 16 Feb 2014, 05:53:06

Finally downloaded this. It should make running my game much easier. Thanks in advance!

User avatar
necklessone
Post Strumpet
Post Strumpet
Posts: 4639
Joined: 25 Jul 2013, 16:13:44
TWG 1 Posts: 0
Location: Somerville, NJ (US Eastern Time Zone)

Re: TWG Vote Tracker - Google Spreadsheet

Postby necklessone » 16 Feb 2014, 08:23:58

If you need any changes for your game; let me know.

User avatar
Hellheart
Post Harlot
Post Harlot
Posts: 2908
Joined: 05 Aug 2013, 09:13:21
TWG 1 Posts: 0
Location: Livonia, MI

Re: TWG Vote Tracker - Google Spreadsheet

Postby Hellheart » 16 Feb 2014, 11:30:09

necklessone wrote:Alright, so here's the new vote tracker I've been working on.

I've considered setting up something similar to pull a vote count from my heatmap worksheet, but I cannot for the life of me figure out how the filter functions under "unsorted vote count" work, let alone how to pull that off in Excel. Just looking a the join function on the right gives me a headache, but it's pointless to even try if the only effective filter I could use in Excel is the Advanced Filter.

User avatar
necklessone
Post Strumpet
Post Strumpet
Posts: 4639
Joined: 25 Jul 2013, 16:13:44
TWG 1 Posts: 0
Location: Somerville, NJ (US Eastern Time Zone)

Re: TWG Vote Tracker - Google Spreadsheet

Postby necklessone » 16 Feb 2014, 12:34:29

The short answer is, as far as I know, Excel can't do what I did the Google spreadsheet. You can probably use some odd-looking array formulas to get partway there, but there's not much functionality for dealing with arrays of strings.

Column R
=UNIQUE(Filter(E3:E,F3:F<>"Y"))
The filter returns all values in Column E (Votee) where the corresponding Column F (Retraction) isn't equal to "Y". This list is fed into the unique function, which takes out duplicates. There's is where Google shines for our purposes; the result of this formula occupies as many rows as it needs to and will auto-adjust according to the results.

Column T
=IF(R3<>"",COUNTIF(Filter(E:E,F:F<>"Y",G:G=0),R3) + SUM(Filter(G:G,E:E=R3,F:F<>"Y")),"")
This formula is filled down manually to cover 100 cells or so, so for the sake of appearance it checks to see if R is blank before returning a non-blank result. The first filter returns all values from Column E (Votee) where Column F (Retraction) isn't "Y" and there's no value in Column G (Vote Weight); it then provides the list to a Countif which returns the number of times the player in Column R was voted for under those circumstances The second filter returns all values from Column G (Vote Weight) where Column E (Votee) is equal to the player in Column R and Column F (Retraction) isn't "Y". I could have done this more elegantly, but it's a case where I did the first part (which assumes each vote counts for one) and then bolted on the second statement (to account for weighted votes) later.

Column V
=IF(R3<>"",Join(", ",Filter(D:D,E:E=R3,F:F<>"Y")),"")
Again this is a manual fill, so I check for a value in Column R before beginning. The filter returns all values in Column D (Voter) where Column E (Votee) is equal to the player in Column R and Column F (Retraction) doesn't equal "Y". This goes into the Join function, which takes the elements of an array and places them into a single string. First I defined the characters I want to be put between each element of the array (", ") and then the array itself.

User avatar
necklessone
Post Strumpet
Post Strumpet
Posts: 4639
Joined: 25 Jul 2013, 16:13:44
TWG 1 Posts: 0
Location: Somerville, NJ (US Eastern Time Zone)

Re: TWG Vote Tracker - Google Spreadsheet

Postby necklessone » 16 Feb 2014, 12:40:03

As I think about it, you could probably set up a pivot table to produce a vote table from your heatmap spreadsheet, depending on how you have the inputs set up. Getting the list of voters to show up as intended may not work, but the worlds ugliest concatenate function full of if statements could do it.

Edit: Maybe an array function using concatenate and if? I'll have to try that in a bit.

User avatar
Hellheart
Post Harlot
Post Harlot
Posts: 2908
Joined: 05 Aug 2013, 09:13:21
TWG 1 Posts: 0
Location: Livonia, MI

Re: TWG Vote Tracker - Google Spreadsheet

Postby Hellheart » 16 Feb 2014, 14:28:34

necklessone wrote:As I think about it, you could probably set up a pivot table to produce a vote table from your heatmap spreadsheet, depending on how you have the inputs set up. Getting the list of voters to show up as intended may not work, but the worlds ugliest concatenate function full of if statements could do it.

Edit: Maybe an array function using concatenate and if? I'll have to try that in a bit.

I tried messing with a Pivot Table when I was setting up everything initially and I couldn't get it to work - putting 3 columns of strings into a PivotTable and expecting it to return something simple is probably unrealistic. My retraction column has the name of the retracted votee instead of "Y" or "N," and the retraction is listed adjacent to the new vote instead of the original vote, which would make for a little puzzle that I'd have fun solving. Both of those are for sanity's sake when dealing with the heatmaps.

User avatar
Hellheart
Post Harlot
Post Harlot
Posts: 2908
Joined: 05 Aug 2013, 09:13:21
TWG 1 Posts: 0
Location: Livonia, MI

Re: TWG Vote Tracker - Google Spreadsheet

Postby Hellheart » 16 Feb 2014, 15:58:10

I can think of a really ugly way to do it - like a really, really long and unwieldy formula that could really benefit from a "loop the following whenever/until [criteria] is true" function. I'd have to list the votees in column A for this to work, but that's not a difficult adjustment. There are other somewhat cleaner ways to do this, but the ones I've seen are array functions and they're both difficult to conceptualize and, from what I gather, much more likely to break from simple adjustments/additions. Oh, and they also don't work with text strings since the ones I've seen use the SMALL function, which isn't a whole lot of help when you're working with text.

I have a separate worksheet for Day 1 called "Data1" that runs the COUNTIF with the full player roster in Column A, and then sorts it in descending order by total votes so that the names in Column A will be correctly transposed into the top row of the heatmap.

The basic idea - after an IF function to check whether total votes for a given name is nonzero - goes like this: the first name is a VLOOKUP function in $A$2:$A$40 for !Data1$A2. The second name is a VLOOKUP function in OFFSET($A$2:$A$40,MATCH(!Data1$A2,$A$2:$A$40,FALSE),0) for !Data1$A2, which should adjust the range for the VLOOKUP function to begin from the row BELOW the first match. Repeat ad nauseum, obviously returning the value in the Voter column every time.

The problem, of course, is that every name beyond the first requires an additional iteration of the OFFSET function. Actually that's not right because the OFFSET would have to be added to the Match result for each iteration, meaning at least two additional nested functions per voter. Is there a function that returns an absolute row reference/number that matches the criteria of a pair of names in adjacent columns? The result would have to be increased by 1 and return as a row reference so it slots into the start of the range.

I know there's a way to make that even cleaner - most likely using an IF or COUNTIF function or both - but the basic concept is sound and that particular format appears to be very robust. Retractions are an entirely different animal, but if I list retractions separately I can probably slip in an IF(EXACT( function that surrounds the name in strike code if true and otherwise simply returns the name. Then I'd CONCATENATE them into one string, unless it's possible to pull off the same sort of thing you used in the Vote History in Excel.

User avatar
necklessone
Post Strumpet
Post Strumpet
Posts: 4639
Joined: 25 Jul 2013, 16:13:44
TWG 1 Posts: 0
Location: Somerville, NJ (US Eastern Time Zone)

Re: TWG Vote Tracker - Google Spreadsheet

Postby necklessone » 16 Feb 2014, 16:51:20

At that level of effort you're probably better off adding a new function via VBA.

User avatar
Hellheart
Post Harlot
Post Harlot
Posts: 2908
Joined: 05 Aug 2013, 09:13:21
TWG 1 Posts: 0
Location: Livonia, MI

Re: TWG Vote Tracker - Google Spreadsheet

Postby Hellheart » 16 Feb 2014, 17:19:18

necklessone wrote:At that level of effort you're probably better off adding a new function via VBA.

I have no coding experience whatsoever, and I like puzzles :P

User avatar
necklessone
Post Strumpet
Post Strumpet
Posts: 4639
Joined: 25 Jul 2013, 16:13:44
TWG 1 Posts: 0
Location: Somerville, NJ (US Eastern Time Zone)

Re: TWG Vote Tracker - Google Spreadsheet

Postby necklessone » 16 Feb 2014, 18:04:16

Hmm.

So let's assume the following workbook structure:
Worksheet "Votes1" - Column A is the Voter, Column B is the Votee, and Column C is the Retractee
Worksheet "Count1" - Column A is the Votee, Column B is a CountIf for Votes!$B:$B minus a CountIf for Votes!$C:$C to get the vote count for each player.

There's no way around having one cell that contains a very long series of concatenated Ifs. What we really want is to make the lookups (or Matches in this case) as clean as possible. I think we can avoid nesting match statements.

Let's use Count1 columns E through Z to track votes with a blank column C & D. We'll assume we're in row 2. And that there's no more than 100 votes between consecutive votes for a given player.

Stick this formula into E2
= SUM($D2:D2) + MATCH($A2, OFFSET(Votes1!$B$1:$B$100,D2,0),0)

That should provide the row of the first match. Fill it to the left and you shift the search range and each cell you go to the left you get the next row that has a vote. When you you run out of votes, it starts returning N/A.

Then in Column AA you could use:
=IF(ISNA(E2),"",INDEX(Votes1!$A:$A,E2)) & IF(ISNA(F2),"",", "&INDEX(Votes1!$A:$A,F2)) & IF(ISNA(G2),"",", "&INDEX(Votes1!$A:$A,G2)) & ...

It's ugly, but you avoid some of the nesting issues. It also doesn't deal with retractions.

User avatar
Hellheart
Post Harlot
Post Harlot
Posts: 2908
Joined: 05 Aug 2013, 09:13:21
TWG 1 Posts: 0
Location: Livonia, MI

Re: TWG Vote Tracker - Google Spreadsheet

Postby Hellheart » 16 Feb 2014, 18:28:39

That's exactly what my workbook would look like if I combined the voted, retracted, and total columns into a single column (which I should do). That's really clever - I never thought about simply calling a cell to replace a whole set of functions that return a number.

I'll see if I can either cut it down or make it more automated tomorrow. I haven't had much any sleep and I'm in a rare creative/inventive mood so I'm theorycrafting roles instead.

User avatar
Hellheart
Post Harlot
Post Harlot
Posts: 2908
Joined: 05 Aug 2013, 09:13:21
TWG 1 Posts: 0
Location: Livonia, MI

Re: TWG Vote Tracker - Google Spreadsheet

Postby Hellheart » 16 Feb 2014, 20:12:28

EDIT: There was an addition problem somewhere in your formula. I'd only gone through 3 iterations for one votee in my testing run, but the 3rd value was well over what it was supposed to be. I adjusted a few things to come up with this formula, which seems to work: =IF(COUNTIF(Hist1!$B:$B,$A2)<COLUMNS($I2:I2),"",H2 + MATCH($A2,OFFSET(Hist1!$B$2:$B$100,H2,0),0)). I'm currently making my tally by using IF (not blank) statements for every vote beyond the first, with TRUE returning a concetenate of that name and a comma with a space.

Ran out of ideas, got bored, started tweaking it. The tallies are going to start from A31 with a blank column B so they won't move above A31 when the whole table is sorted in descending order using Column B.

I prefaced your the E2 formula with the following: IF(COUNTIF(Hist1!$B:$B,$A2)<COLUMNS($E2:E2),"",______). I believe this says "if there are more columns than votes, keep this blank. Otherwise, continue with the formula." That solves the N/A problem.

I moved the start of the formula to H2 to make room for retractions. I could use the same formula for those, but I'd end up with a string containing multiple names and that doesn't work with EXACT. I'm just going to manually enter them in now. I can strike out a retraction with the following: =IF($C2<>"",SUBSTITUTE($A31,$C2,CONCATENATE("{strike}",$C2,"{/strike}")),""). Unfortunately, that only runs one substitute, and I can't run multiple substitutes.

This is where Visual Basic really is the way to go, but screw that. I'm going to eventually assume a maximum of 3 retractions and set up a series of logic tests to determine which is the furthest left and which is the furthest right (and whether I have to care about a 3rd one at all), but for now I'll just enter in the retractions from furthest left to furthest right in the tally. Then I just need to set up an ENORMOUS Concatenate to automate this. For example, let's say that there were 2 retractions for Player A2. These retractions are in C2 and D2, but D2 comes earlier than C2 in the tally. I can strike out both names using the following function, which involves neither the 3rd potential retraction nor the additional IF statements to ensure that only having two or three retractions won't break the formula.

Code: Select all

=CONCATENATE(LEFT($A31,SUM(FIND($D2,$A31),-1)),"[strike]",$D2,"[/strike]",MID($A31,SUM(FIND($D2,$A31),LEN($D2)),SUM(FIND($C2,$A31),-SUM(FIND($D2,$A31),LEN($D2)))),"[strike]",$C2,"[strike]",MID($A31,SUM(FIND($C2,$A31),LEN($C2)),300))

That was way more fun than it should have been. Now that I have a template, I'm going to take a break and set up the formula for the tally itself, assuming I don't fall asleep. Then I can go back and make room for $E2, and have IF functions to determine whether D2 and E2 are at the end, if either exist at all.

User avatar
necklessone
Post Strumpet
Post Strumpet
Posts: 4639
Joined: 25 Jul 2013, 16:13:44
TWG 1 Posts: 0
Location: Somerville, NJ (US Eastern Time Zone)

Re: TWG Vote Tracker - Google Spreadsheet

Postby necklessone » 17 Feb 2014, 04:36:34

Looking over the formula with fresh eyes, the sum should just be "D2" instead. I started typing it up with the idea that each cell contained the offset from the previous and then mid way changed course so each cell had the row number.

User avatar
Hellheart
Post Harlot
Post Harlot
Posts: 2908
Joined: 05 Aug 2013, 09:13:21
TWG 1 Posts: 0
Location: Livonia, MI

Re: TWG Vote Tracker - Google Spreadsheet

Postby Hellheart » 17 Feb 2014, 13:02:12

You're right - I switched the offset back to $B$1:$B$100 and it worked perfectly. I adjusted the tally's nested concatenates by moving the comma to the front of the name so there's not an ugly trailing comma at the end of it. Once I copy your formula across 10-ish columns of Row A, all I have to do is populate that downwards and I'll have an automated tally sans retractions without having to do a damn thing. Oh, I also added an IF statement at the front of the tally that returns "" if nobody cast a vote for that player. I can just highlight however many cells have tallies, copy, paste here, and then submit. Easy-peasy.

I already enter Penalty Votes as voters at the start of the day, so that will be reflected as well.

Retractions will still take a bit more work, though. I'm going to start working on the logic for that now, since when that's done I'll have a fully-functional automated tally creator if you ignore my having to manually key in retractions. Upon reflection it wouldn't be difficult to use your formula for retractions as well and simply use the index function instead of calling the cell directly, but the retractions won't be in left-to-right order and if you think my retraction concatenate is big now, you have NO idea how big it would be if I had to include AND/OR logic functions to determine which retraction is the furthest to the left and right in the tally.
Last edited by Hellheart on 17 Feb 2014, 15:57:04, edited 3 times in total.

User avatar
Hellheart
Post Harlot
Post Harlot
Posts: 2908
Joined: 05 Aug 2013, 09:13:21
TWG 1 Posts: 0
Location: Livonia, MI

Re: TWG Vote Tracker - Google Spreadsheet

Postby Hellheart » 17 Feb 2014, 15:27:04

Got it. Here's my finished retraction code, assuming maximum of 3 retractions. The initial "IF" statement returns the unmodified tally if there are no retractions, so again I can just copy right down and paste into the thread. I have this in cell C31, again leaving B empty so the Sort doesn't ruin everything.

Code: Select all

=IF($C2<>"",CONCATENATE(LEFT($A31,SUM(FIND($C2,$A31),-1)),"[strike]",$C2,"[/strike]",IF(D2<>"",CONCATENATE(MID($A31,SUM(FIND($C2,$A31),LEN($C2)),SUM(FIND($D2,$A31),-SUM(FIND($C2,$A31),LEN($C2)))),"[strike]",$D2,"[/strike]",IF($E2<>"",CONCATENATE(MID($A31,SUM(FIND($D2,$A31),LEN($D2)),SUM(FIND($E2,$A31),-SUM(FIND($D2,$A31),LEN($D2)))),"[strike]",$E2,"[/strike]",MID($A31,SUM(FIND($E2,$A31),LEN($E2)),300)),MID($A31,SUM(FIND($D2,$A31),LEN($D2)),300))),(MID($A31,SUM(FIND($C2,$A31),LEN($C2)),300)))),$A31)


That automates everything except for my manually putting the retractions into columns C-E. I suppose that's good enough for now :lol:

User avatar
Hellheart
Post Harlot
Post Harlot
Posts: 2908
Joined: 05 Aug 2013, 09:13:21
TWG 1 Posts: 0
Location: Livonia, MI

Re: TWG Vote Tracker - Google Spreadsheet

Postby Hellheart » 25 Feb 2014, 02:48:42

Since I have time now, I was figuring out how to automatically color-fill any cell that contained a known human or wolf. Conditional formatting is wierd, particularly since relative references work off of the location of the upper-leftmost cell in the array you're applying the formatting to - something that Excel neglects to mention ANYWHERE in the Conditional Formatting Help section. Also, Conditional Formatting only works with values in the current worksheet, so that was "fun" to deal with.

So I have a new worksheet - "Factions" - with column A labeled "wolves" and column B labeled "humans". In every other worksheet I have the following array formula in AA1:AA30 and a similar one for AB1:AB30: {=IF(Factions!A1:A30="","",Factions!A1:A30)}. This simply copies the two columns to every other worksheet so I can use conditional formatting.

Of course, TRUE/FALSE is not so easy when you're dealing with text - the MATCH function returns #N/A instead of FALSE if there's no match. Also, using MATCH on a blank cell will virtually always return "TRUE" unless the array I'm matching with is completely filled with names. After probably a good hour of tinkering with formulas that stubbornly refused to do what I wanted them to do, I got one that worked for the humans and got the syntax right: =IF(A1<>"",NOT(ISNA(MATCH(A1,$AB$1:$AB$30,0))),FALSE)

Obviously worth the effort. Why spend at most a minute manually coloring names in when I can spend an hour figuring out how to do it automatically? :lol:

User avatar
dferrantino
Post Nympho
Post Nympho
Posts: 5531
Joined: 25 Jul 2013, 13:37:12
TWG 1 Posts: 0
Location: NYC (EST/GMT-5)

Re: TWG Vote Tracker - Google Spreadsheet

Postby dferrantino » 25 Feb 2014, 07:18:07

Maximum of 3 retractions, eh?

User avatar
Hellheart
Post Harlot
Post Harlot
Posts: 2908
Joined: 05 Aug 2013, 09:13:21
TWG 1 Posts: 0
Location: Livonia, MI

Re: TWG Vote Tracker - Google Spreadsheet

Postby Hellheart » 25 Feb 2014, 09:56:53

dferrantino wrote:Maximum of 3 retractions, eh?

I can always add more. The code for the 2nd retraction can be repeated indefinitely, since its calculation uses a MID() function that starts from the end of the previous retraction and covers the distance between the two. I just settled on three because it lets me mess around with potential code to automate that. So far I've had zero success on that front because I have no intention of using a ton of logic functions in an already super-long function.

I might be able to use the "Factions" sheet to house a bunch of functions to determine retraction order. The difficulty isn't in finding them - that's the same code that's used for the voters - the difficulty is that the resulting rows refer to the position of the new vote, rather than the old one. So I need to then pull Voter from that row, match that to the previous instance of that Voter, pull THAT row, and only then can I attempt to order the retractions from left-to-right in the tally.

The above is much harder when you realize that "previous instance" does not always mean "first instance," so I can't use a simple lookup function either. I might be able to use a PivotTable for that; I'll have to mess around with it.

User avatar
Hellheart
Post Harlot
Post Harlot
Posts: 2908
Joined: 05 Aug 2013, 09:13:21
TWG 1 Posts: 0
Location: Livonia, MI

Re: TWG Vote Tracker - Google Spreadsheet

Postby Hellheart » 26 Feb 2014, 00:33:38

Improved my retraction function significantly. Had NO idea that nested substitutes worked as well as they do - it just never occured to me. This works for 5 retractions and can easily be extended indefinitely:

Code: Select all

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A31,$C2,CONCATENATE("[strike]",$C2,"[/strike]")),$D2,CONCATENATE("[strike]",$D2,"[/strike]")),$E2,CONCATENATE("[strike]",$E2,"[/strike]")),$F2,CONCATENATE("[strike]",$F2,"[/strike]")),$G2,CONCATENATE("[strike]",$G2,"[/strike]"))

User avatar
Hellheart
Post Harlot
Post Harlot
Posts: 2908
Joined: 05 Aug 2013, 09:13:21
TWG 1 Posts: 0
Location: Livonia, MI

Re: TWG Vote Tracker - Google Spreadsheet

Postby Hellheart » 27 Feb 2014, 04:56:36

Great success! Nested substitutes means that the order of retractions is now irrelevant, which means I can automate those now as well. My entire voting sheet is now completely automated - all I have to do is enter the votes and retractions when they're cast. I don't miss retractions anymore either because it'll auto-complete the name of a player who has already voted that day, which prompts me to look at who he previously voted for and key that into the Retractions column.

Right now, after I key in a vote, it does the following:
  • Automatically updates vote tallies, including striking out retracted votes.
  • Counts the votes for each player, so I can Sort Descending the tally before posting (this will also correctly sort the names on the Heatmap)
  • Places the new voter in the next row in the heatmap, updates all of the numbers, and uses Color Scales to color them appropriately

And now I can put a name under the "wolf" or "human" column in the Faction spreadsheet, and every cell that contains that name (except for the tallies) is filled in red or blue, respectively, so I don't have to worry about keeping up with the coloring either.

I'm really happy with the way it is right now - it used to take me more than 30 minutes to do all of that manually after every EOD. Now I just enter in the new votes when I check the thread and it'll all be done for me. And as a bonus, I have up-to-date voting tallies and heatmaps. I also have voting histories that clearly indicate confirmed wolf and human players, which is something I've never had before and it's actually quite nice.

User avatar
Hellheart
Post Harlot
Post Harlot
Posts: 2908
Joined: 05 Aug 2013, 09:13:21
TWG 1 Posts: 0
Location: Livonia, MI

Re: TWG Vote Tracker - Google Spreadsheet

Postby Hellheart » 02 Mar 2014, 17:01:46

I'll probably directly ask the players that like to do voting tallies, but PM me If you'd like a copy of this once I'm done with it. It's more analysis-focused than Necklessone's tally, so IMO it makes more sense for players. Especially since it now automates the color code.

I'm pretty much done with the coding - I've switched the two row-counting functions to array formulas and extended them out, and I'm in the process of looking at the remaining functions, cleaning them up if I can, and increasing their scope if appropriate. Really all that's left to do besides that is plug in 3 or 4 more columns in Factions - for games with Neutrals and multiple teams - and have the color tally function substitute for about 6 names in each (which will, of course, make it even larger than its current size of ~2250 characters without spaces). Once I'm done with that I'll move everything over to my template and create sheets for about 8 days with the proper sheet references.

User avatar
Hellheart
Post Harlot
Post Harlot
Posts: 2908
Joined: 05 Aug 2013, 09:13:21
TWG 1 Posts: 0
Location: Livonia, MI

Re: TWG Vote Tracker - Google Spreadsheet

Postby Hellheart » 25 Mar 2014, 23:35:20

Spoilered what was here for my own future amusement, but the answer to this problem was: the only way to do this with an array formula requires INDIRECT, which is a volatile function, and as such it should never be used in any formula that's going to apply to thousands of cells.

So the best solution is to use the very simple, incredibly fast non-array formula I devised. I now have a "corrections" worksheet for the sole purpose of copyiing data back and forth to fix missed votes without breaking references. It's a bit of extra work, but it's not that bad.

[+] Stupid rigid array formulas
Since it's been awhile since I've actually asked for help here, I'd like to reiterate that I'm looking for solutions using only Excel formulas - no SQL coding. Creating array formulas for the template have actually been going swimmingly outside of this one issue.

The problem is simple: the switch to array formulas either maintained or decreased the size of all of my formulas except for one: the heatmap formulas, which have roughly doubled in size and quadrupled in the number of functions per cell. This has significantly slowed down calculation speeds, which annoys the hell out of me. I expected some slowdown because the new array formulas cover roughly twice as many rows, but this is way more than I bargained for and the heatmap formula is the only one that needs a convoluted workaround to function. Of course it's also the only place where I really need an array formula because right now I have to go fix 10+ columns of broken heatmap references every time I add a row in History to plug in a vote that I missed.

The pair of formulas in this code tag are the original formulas, where the first formula goes in Row 2 and the other formula goes everywhere else. The third line of code is a major improvement I hit on today - that formula works for every row, blanks out every cell after the last vote so it looks nicer, and only uses two functions instead of four for the actual calculation.

Code: Select all

=IF(EXACT(D$1,Hist1!$B$2),1,0)
=SUM(D2,IF(EXACT(D$1,Hist1!$B3),1,0),IF(EXACT(D$1,Hist1!$C3),-1,0))

=IF(Hist1!$B2="","",SUM(COUNTIF(Hist1!$B$2:$B2,B$1),-COUNTIF(Hist1!$C$2:$C2,B$1)))

The pair of formulas follows a very simple procedure: input the number from the preceding cell. If the name in "voted" in this row matches the name that heads this column, add one. If the name in "retracted" in this row matches the name that heads this column, subtract one. Output the result. The revised formula is a vote-counting formula that only looks at the votes and retractions up to and including that row.

Of course, none of those will work as array formulas. The last one ALMOST works after I remove SUM and let it run as a naked equation, but there's a very simple snag that's maddeningly difficult to fix: array formulas don't increment relative cell references within a range because the range is what defines the array in the first place. That may sound really confusing without an example, so here's two possible versions of the 100-row, "array-friendly" version of that last formula:

Code: Select all

{=IF(Hist1!$B2:$B100="","",COUNTIF(Hist1!$B$2:$B2,B$1)-COUNTIF(Hist1!$C$2:$C2,B$1))}
{=IF(Hist1!$B2:$B100="","",COUNTIF(Hist1!$B$2:$B100,B$1)-COUNTIF(Hist1!$C$2:$C100,B$1))}

In the first formula, all 100 cells look at the range B2:B2 so the whole column is 0 or 1. In the second formula, all 100 cells look at the range B2:B100 so the whole column is that player's current vote total. As far as I can tell, there's no easy fix for this problem because Excel will always treat that range as part of the array function.

There is a very clever workaround that works even though this is a cross-worksheet reference. Problem is, it adds SIX additional functions per cell, across 1200 cells.

Code: Select all

{=IF(Hist1!$B2:$B100="","",SUM(0,COUNTIF(Hist1!$B$2:INDIRECT(ADDRESS(ROW(),2,3,,"Hist1")),B$1),-COUNTIF(Hist1!$C$2:INDIRECT(ADDRESS(ROW(),3,3,,"Hist1")),B$1)))}

I can just imagine the array formula logic freaking out and going "WHAT THE HELL IS THIS MONSTROSITY WHERE IS MY RANGE," but I'm pretty sure the actual issue is that I'm forced to use 3 functions to create a single relative reference within a range...and I need to do that twice. ROW() correctly increments the reference with the least amount of fuss, but that then needs to be fed into ADDRESS to create a reference. Unfortunately, ADDRESS outputs the reference as a text string, so ranges just error out unless I use INDIRECT to turn it into a "true" reference.

So my question is: is there a way to do this that only adds 2 or 4 functions?

Despite all that griping, I'm genuinely impressed that this trick works at all. You can even use it for both parts of the range, which is what I originally did (TWELVE more formulas per cell!), but thankfully the external reference value for ADDRESS didn't have the expected conflict with the external reference I use to start the range.

User avatar
Hellheart
Post Harlot
Post Harlot
Posts: 2908
Joined: 05 Aug 2013, 09:13:21
TWG 1 Posts: 0
Location: Livonia, MI

Re: TWG Vote Tracker - Google Spreadsheet

Postby Hellheart » 03 Apr 2014, 08:43:05

All my problems seem to stem from my ignorance of these so-called "volatile formulas." I mean, look at this: I have a completely useless INDIRECT in my vote-finding array function (you see Perry the Platypus, INDIRECT is necessary when using ROW to increment within arrays, but not when using COLUMN). I had even planned to put conditional formatting on EVERY SHEET IN THIS TRI-SHEET WORKBOOK! I was wondering why my old spreadsheets acted odd when I scrolled up or down.

(( Sorry, sorry, I looked at my avatar after posting this the first time and I just couldn't help myself. it had to be done. ))

I've cut out the INDIRECT (and adjusted the function to just return the name of the voter instead of the row number) and the speed has increased immeasurably. Now all I need to do is write a VBA subroutine that, when called, will fill the cells in that worksheet that match a name in "Factions" with the appropriate color. That way, I can just run the function once when a role is revealed.

Yeah, that's right, "all I need to do is write a VBA subroutine" for conditionally formatting cells. I've mucked around with VBA a bit (it was the only way to make the color-code substitute function manageable), and there should be an example I can almost copy verbatim for this one, but it just takes so much time for me.

------

Actually, mulling over it now, I'm not sure that INDIRECT is strictly necessary even when using ROW() to increment. You could just do SUM(ROW(2:2)-ROW(1:1)) to ensure that the starting value is always "1." The suggestion I always see is "use INDIRECT() so the number doesn't change!" Which is great if you love clean-looking functions and don't mind having the cell recalculate whenever data changes anywhere in the entire GD worksheet.

sphenodont
Overlord
Overlord
Posts: 4345
Joined: 25 Jul 2013, 05:36:16
TWG 1 Posts: 1671
TWG 2 Posts: 737
Location: Bloomington, IL (Central Time)

Re: TWG Vote Tracker - Google Spreadsheet

Postby sphenodont » 17 May 2017, 12:52:22

Last post was three years ago, so unstickying.

User avatar
necklessone
Post Strumpet
Post Strumpet
Posts: 4639
Joined: 25 Jul 2013, 16:13:44
TWG 1 Posts: 0
Location: Somerville, NJ (US Eastern Time Zone)

Re: TWG Vote Tracker - Google Spreadsheet

Postby necklessone » 17 May 2017, 16:44:52

Seems reasonable


Return to “The Village Pub III”

Who is online

Users browsing this forum: No registered users and 1 guest