GeekGold Bonus for All Supporters at year's end: 1000!

7,934 Supporters

$15 min for supporter badge & GeekGold bonus
21 Days Left

Support:

Recommend
4 
 Thumb up
 Hide
24 Posts

BoardGameGeek» Forums » Gaming Related » Conventions » WBC

Subject: GM assistant spreadsheet rss

Your Tags: Add tags
Popular Tags: [View All]
Andy Latto
United States
Foxboro
Massachusetts
flag msg tools
designer
mbmbmbmbmb
The 2016 Edition of my 2016 GM assistant spreadsheet is ready, and available at http://tinyurl.com/wbc-gm-2016. The spreadsheet helps with three things;

1. It assists with signin, getting the name from the badge number so you only need to enter the number, and checking against the registration list to verify that the person is registered for that day.

2. It helps with computing qualifying order. It has a customizable point system for awarding points for how players placed in their game, and their relative scores. You enter the point values you're using for your game, and the scores by badge number, and it will compute the top qualifiers for the elimination rounds. Not every possibly qualification system can be handled, but a lot can. If your formula distinguishes between a win in first heat entered and a win in a later heat, you can give different point values for those, and the spreadsheet will automatically apply the right one if you used the spreadsheet for signin, since it knows whether a winner has played in a previous heat.

3 It prepares most of the event form, filling in names, badge numbers, and which heats were entered. You just have to add in the elimination rounds in counting total games and total player starts, and fill in the top 6.

There's a little setup work to do to make sure the spreadsheet works when offline, which I encourage you to do before you head to Seven Springs: While I hope the internet access there is better than at the host, I don't want to rely on having good internet access in every room where tournaments are being held. So please make a copy, follow the instructions, and play with it a bit before WBC starts. If you have any questions or suggestions for improvements, please contact me at andy dot latto at pobox dot com. If you haven't used the spreadsheet before and are planning to use it this year, please send me an email; that way if I discover any last-minute bugs, I can make sure to notify you.

WBC starts in 12 days! I can't wait!


8 
 Thumb up
1.75
 tip
 Hide
  • [+] Dice rolls
Andrew E
United States
flag msg tools
mbmb
If you are put off by the response time between entering a badge number and the sheet updating the name and paid status, a solution is to download the sheet as xlsx and run it in Excel, where it updates basically instantly. I'm not sure if the printable sign in sheet will look correct from Excel, but if not, it's easy enough to paste the data all back into the google sheet form.
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Andy Latto
United States
Foxboro
Massachusetts
flag msg tools
designer
mbmbmbmbmb
About half the slow performance seems to be connected with the conditional formatting, which turns the "Add to Master?" and "Paid" columns onn the Heat signin sheets red when the badge number is not found or the person hasn't paid for the appropriate day. If you turn off the conditional formatting, it speeds up quite a bit.

To turn it off, click on a cell with the conditional formatting (say cell C3 on the heat signing sheet), go to the "Format" menu and select "Conditional Formatting". The conditional formats appear on the right, and you can click on the trash can to delete them. The repeat for cell C4.

It's still not instantaneous, but is substantially faster if you do this.
2 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Max Jamelli
United States
Chambersburg
Pennsylvania
flag msg tools
designer
badge
Avatar
mbmbmbmbmb
Thanks Andy - always a big help.

I've grown accustomed to downloading and running locally since there was always questions of wi-fi at the host. I'm assuming (maybe incorrectly) that 7S will have wifi.

If there is an update, I assume a Google share email will go out?
2 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Andy Latto
United States
Foxboro
Massachusetts
flag msg tools
designer
mbmbmbmbmb
I don't know if there's an easy way in google drive to do "send email to everyone I've shared this document with"; do you know how to do that?

In any case, I will post here, and will change the version number on the spreadsheet, if I find anything that needs to be fixed.

I wanted to post about the spreadsheet on consimworld, but I can't remember my password, and password resets and account creation seem to be manual processes that haven't happened yet. Could someone else post about the spreadsheet on the consimworld WBC forum for me?
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Max Jamelli
United States
Chambersburg
Pennsylvania
flag msg tools
designer
badge
Avatar
mbmbmbmbmb
I don't know if Google has that option, but I subscribed to this thread so that'll work too.
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Max Jamelli
United States
Chambersburg
Pennsylvania
flag msg tools
designer
badge
Avatar
mbmbmbmbmb
andylatto wrote:
Could someone else post about the spreadsheet on the consimworld WBC forum for me?


I copied and pasted your post on CSW.
3 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Andrew E
United States
flag msg tools
mbmb
I used your sheet, and I was happy with it. I did end up exporting it to excel and then pasting the data back to get the printable event form (how does that currently work? I don't see any formulas or scripts making that happen). You did show me the response time for you, and making it available offline does seem to improve the response time to something I'd consider usable, so in the event I get lazy and don't do any of my ideas, I'll probably just do that next year.

I did get the side-eye when I turned in the event form. I naturally protested that I thought it had been blessed by management (and I was and am under the impression that it was), and it seems like the major complaints were that it doesn't have the prize level on it and it's different.

Hopefully we can work out something even better for next year. I'll probably add a check to the sign in sheets warning of duplicates, and possibly a place to nicely fill in the rest of the information that the event form needs so it can be entirely typed without needing to actually ever touch it from a user's perspective.

On the other hand, if we can get management on board, I'd be even more in favor of somehow delivering them an appropriately formatted text file that they can script directly into their database. An event form of some sort would still be required for prize purposes, but maybe it need only include the event, prize level, and top 6 finishes.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Geoffrey Burrell
United States
Cedar Rapids
Iowa
flag msg tools
There are also GM computer programs that aid in initiative order, keep track of enemy HP, and roll dice. I forget which on my group's regular GM uses. One might be called GM's Familiar.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Andy Latto
United States
Foxboro
Massachusetts
flag msg tools
designer
mbmbmbmbmb
AndrewE wrote:
I used your sheet, and I was happy with it. I did end up exporting it to excel and then pasting the data back to get the printable event form (how does that currently work? I don't see any formulas or scripts making that happen). You did show me the response time for you, and making it available offline does seem to improve the response time to something I'd consider usable, so in the event I get lazy and don't do any of my ideas, I'll probably just do that next year.

I did get the side-eye when I turned in the event form. I naturally protested that I thought it had been blessed by management (and I was and am under the impression that it was), and it seems like the major complaints were that it doesn't have the prize level on it and it's different.

Hopefully we can work out something even better for next year. I'll probably add a check to the sign in sheets warning of duplicates, and possibly a place to nicely fill in the rest of the information that the event form needs so it can be entirely typed without needing to actually ever touch it from a user's perspective.

On the other hand, if we can get management on board, I'd be even more in favor of somehow delivering them an appropriately formatted text file that they can script directly into their database. An event form of some sort would still be required for prize purposes, but maybe it need only include the event, prize level, and top 6 finishes.


Glad it was useful!

When I turned in my event form, the person I turned it in to is the new CD, Ken Gutermuth. He seemed perfectly happy with it except for the fact that the prize level was missing, so I will certainly add a space for that next year. More generally, I will talk to him about what he cares about and doesn't care about in matching the existing event form. For example, I'd like to have more columns in the "what heat did this person play" section, so there was room for 3 heats, QF, SF, and F.

Planned enhancements for next year:

1. Improvements to event form (see above)

2. Duplicate detection, unless it slows things down too much. In one of my heats, I entered a few people for testing purposes before starting checkin, then forgot to delete them. This led to there being one too many table, which I was able to fix fairly quickly, but still it would have been nice to have duplicate detection prevent this problem.

3. Addition of a fourth heat. I plan to use this for the quarterfinals for Thurn and Taxis.

4. Performance improvement. I have various ideas for things that might speed things up a bit, and I'm hoping that one of them will have a big impact.

5. Any requests? Anything I can do to make the spreadsheet easier for you to use for your tournament?

Possible un-enhancements:

1. Removal of the "Is this person registered for this day?" code. With the elimination of the pre-con and the new venue, 90% of registrants pay for the full week. So I might just have the checkin distinguish the three categories "Pregristered for the week", "Preregistered, but not for the whole week", and "Not preregistered", and the GM can check the badge for the last two categories. If this speeds things up substantially, which it might, then I think it's worth making the GM check 1 or 2 more badges per heat if it speeds up all the rest of the checkins.

2. Win in first heat entered. The spreadsheet currently looks up whether a winner has won in their first heat entered, and allows a different number of qualifying points depending on this. Thurn and Taxis no longer uses this information; when I went to a quarter-final/"win and you're in" format, I switched to a formula that doesn't distinguish wins in first heat entered. If removing this from the spreadsheet speeds things up substantially, I may remove this capability from the spreadsheet. If you use this, please let me know; if there are users for this, but removing it helps performance, I may produce two separate versions of the spreadsheet next year, one with, one without, this feature.

1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Andrew E
United States
flag msg tools
mbmb
Looks like I don't have write access to the file. I sent along a request, but here's what I did in my little burst of inspiration, duplication detection:

=if(A3 = "", "", if(countif(A$3:A$500, "="&A3) = 1, "OK", "Duplicate"))

Other things I might add would be a way to calculate number of games (requiring number of players per game and whether you're rounding up or down), and a little form to fill out that puts everything onto the event form. (This would include the game title, the prize level, the 6 finishers, the number of elimination games (usually 5), and an override for the total number of games in case you had to fudge it somewhere.

That would then make the event form totally filled out without having to touch it.
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
norman rule
United States
Columbia
Maryland
flag msg tools
designer
badge
Avatar
mbmbmbmbmb
Andy,

We've used your spreadsheet for two years for Saint Peterburg, and it makes check-in an easy process. Thanks!

One thing that I've done the past two years is to point the VLOOKUP on the results page back to the sign in page for that heat. This makes sure that only people who signed up get scores. If it comes up as an N/A, they didn't play and you probably fumble fingered the badge number while entering results.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Doug Faust
United States
Malverne
New York
flag msg tools
badge
Avatar
mbmbmbmbmb
I'll add that Navegador used this (I'm the assistant GM, my wife is GM). It was actually a last minute decision because I found out that the Access database that we had been using for the past couple of years to do standings became corrupted somehow and was unusable (and I found this out the Thursday before the convention). Some comments:

1. I imported this into Excel and used it that way (based on Andrew posting that it worked). The only issue I had was that I couldn't sort the Master tab without it screwing up all the references. Nothing that couldn't be worked out with a little copy/paste though.

2. I did not use the included event form because I couldn't figure out how to populate it. Maybe that's something that would have worked in Sheets too.

3. There was some stray data in the "heat 2 sign-in" tab, which caused the total player count at that top of that tab to appear as four more than it should've been. (It took me a good 10 minutes to figure out what was going on here...)

4. There was no way to record results for a fifth player at a game. Obviously this has little impact on standings, but it would've been nice to have for posterity's sake.

5. If the "win in first heat" tiebreaker was removed, the entire spreadsheet would have been unusable. That's still the standard WBC tiebreaker.

In any case, I hope to be able to restore my database for 2017, but it was really nice having a fallback option that didn't involve trying to figure out everything out by hand. Thanks!
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Eric Freeman
United States
Richboro
Pennsylvania
flag msg tools
mbmbmbmbmb
I used the sheet for Vegas Showdown. I pretty much used it as is, but just modified the point totals for my event.

I used it for

1) check-in
2) qualifier sorting (went to the Master and sorted by score)
3) print out of the event form to turn in - my only issue there was the GM printer either wasn't functioning correctly or did not like the printer output from the sheet. I got blank lines across the paper at intervals which just meant I had to fill in by hand the areas that were not legible.

All in all it was great. I had no issues with the speed whatsoever.
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Andy Latto
United States
Foxboro
Massachusetts
flag msg tools
designer
mbmbmbmbmb
mrorwell wrote:
Andy,

One thing that I've done the past two years is to point the VLOOKUP on the results page back to the sign in page for that heat. This makes sure that only people who signed up get scores. If it comes up as an N/A, they didn't play and you probably fumble fingered the badge number while entering results.

A good idea! I will add that for next year.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Andy Latto
United States
Foxboro
Massachusetts
flag msg tools
designer
mbmbmbmbmb
efreeman wrote:
I used the sheet for Vegas Showdown. I pretty much used it as is, but just modified the point totals for my event.


3) print out of the event form to turn in - my only issue there was the GM printer either wasn't functioning correctly or did not like the printer output from the sheet. I got blank lines across the paper at intervals which just meant I had to fill in by hand the areas that were not legible.

I used the 7 springs printer rater than the GM printer; there was a room just off the main lobby (if you're looking at the Concerge desk, it's on your right) with two computers, one of which was hooked up to a printer. With that printer, the event from printed out fine. Next year I'll try to print out with the GM printer and see if I can find out what the problem is, but if you have this problem next year, use the 7 Springs printer instead.

1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
David Finberg
United States
Wilmington
Massachusetts
flag msg tools
mbmbmb
Again, many thanks for doing this.

As a minor note, and mostly suggested above, adding space for quarter/semi/final results allows a simple way to send results for AREA bookkeeping, not that I really care that much about it.

I'd need to think about how it might work, but a quick table setup column might be nice. Enter table leaders during checkin, and when done it randomly assigns folks to tables. I think it would be simple for most, but I tend to run multiple game types in the trucker early rounds which complicates it.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Andy Latto
United States
Foxboro
Massachusetts
flag msg tools
designer
mbmbmbmbmb
dfinberg wrote:

I'd need to think about how it might work, but a quick table setup column might be nice. Enter table leaders during checkin, and when done it randomly assigns folks to tables. I think it would be simple for most, but I tend to run multiple game types in the trucker early rounds which complicates it.


I've thought about doing this, but don't see how it would help. The GM would still have to read out the assignments, and giving everyone a seat card and having them find their tables seems faster.
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Michael McKibbin
United States
Clemmons
North Carolina
flag msg tools
badge
Avatar
mbmbmbmbmb
We used the GM Assistant spread sheet (in Excel) for Five Tribes sign-in and event reporting. We used a custom spreadsheet for scoring and ranking the event itself, but it was very easy to transfer the necessary data back and forth between spreadsheets as necessary.
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Andy Latto
United States
Foxboro
Massachusetts
flag msg tools
designer
mbmbmbmbmb
hgman3 wrote:
We used the GM Assistant spread sheet (in Excel) for Five Tribes sign-in and event reporting. We used a custom spreadsheet for scoring and ranking the event itself, but it was very easy to transfer the necessary data back and forth between spreadsheets as necessary.

Glad the spreadsheet was helpful.

What additions to the GM Assistant spreadsheet would you need to be able to use it for scoring and ranking? I'm glad transferring data back and forth was easy, but it would be nice to render this unnecessary. And while it was easy for you, someone running a similar event with less spreadsheet-savvy might have found it more difficult.

1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Michael McKibbin
United States
Clemmons
North Carolina
flag msg tools
badge
Avatar
mbmbmbmbmb
andylatto wrote:
hgman3 wrote:
We used the GM Assistant spread sheet (in Excel) for Five Tribes sign-in and event reporting. We used a custom spreadsheet for scoring and ranking the event itself, but it was very easy to transfer the necessary data back and forth between spreadsheets as necessary.

Glad the spreadsheet was helpful.

What additions to the GM Assistant spreadsheet would you need to be able to use it for scoring and ranking? I'm glad transferring data back and forth was easy, but it would be nice to render this unnecessary. And while it was easy for you, someone running a similar event with less spreadsheet-savvy might have found it more difficult.



I would have liked to have used the GM Assistant for all of our GM needs, but this year we simply didn't have the time to make it happen. I only downloaded the software about a week before we left for Seven Springs), and my wife had already put together a custom spreadsheet in Excel to handle our scoring and ranking. This didn't give us much time to exploring the possibility of integrating the two spreadsheets, so we used both. Given the abundance of time before next year's WBC, I should now be able to see what the GM Assistant can do for me in terms of scoring and ranking. I'm also considering a couple tweaks to my ranking formula for next year, so that is also something I will need to take into consideration. Sorry I don't have an immediate answer, but I'll definitely work with it over the next few weeks and will let you know if I have any questions or suggestions.
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Henry Richardson
United States
Virginia
flag msg tools
mbmbmb
Andy:

I have found the GM Assistant invaluable over the past couple of years. I run a very simple Swiss tournament for a two-player game, so scoring was not something I looked to the Assistant to help with. I used it for signing folks in with a minimum of fuss and delay, and also for populating the Event Form to turn in after the event was over.

I don't use Google Sheets, so I take a week or so to find out why some of the formulae don't work in Excel (actually OpenOffice in my case), and tweak them until they do.

I took your "worksheet" worksheet concept and enhanced it a bit. I created a sheet called "Players", and store badge numbers, player names, "has copy of game?" answers, and populate heat attendance -- all the stuff I need for the event form. I even added in formulae to capture statistics for the Event Form -- number of players, total games, and player starts. (Remember, 2-player game, so simple math!) Almost all I really need to do on the Event Form manually is enter the top six finishers. The only other manual efforts involve players who first come to play in later rounds -- that involves a little on-the-fly work on the Players sheet.

All that I have done is just rearranging deck chairs on the Titanic compared to the work you have done in creating and refining the GM Assistant to begin with. Many many thanks for your creativity and persistence!

Henry

1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
norman rule
United States
Columbia
Maryland
flag msg tools
designer
badge
Avatar
mbmbmbmbmb
Andy,

Another thing I've added to ours is to add a calculation at the top of the sign in page that tells me how many tables I have. Mine is hard coded, but you could have an entry field for the ideal number of players at a table, then use that with a count formula to tell you how many tables you have.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Andy Latto
United States
Foxboro
Massachusetts
flag msg tools
designer
mbmbmbmbmb
Good idea!
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Front Page | Welcome | Contact | Privacy Policy | Terms of Service | Advertise | Support BGG | Feeds RSS
Geekdo, BoardGameGeek, the Geekdo logo, and the BoardGameGeek logo are trademarks of BoardGameGeek, LLC.