Excel quiz bowl scoresheets

[lchen]

http://sites.google.com/site/hchsquizbowl/Home/excel-scoresheets

Here are some Excel spreadsheets I wrote that can be used as quiz bowl scoresheets for mACF, NAQT, or PACE formatted games. They're good for practices or tournaments if your coach brings a laptop.

They do all the math automatically, and have error-detection for common mistakes such as typing 19 instead of 10, etc. They also have dynamic highlighting to make it easier to see which tossups went dead, or if you accidentally skipped a row when entering scores. The mACF version supports 15-point powers and a tiebreaker tossup.

I would appreciate any comments or feedback!

[Mechanical Beasts]

That's some pretty sexy Excel programming. Also, you know the word "kludge," so +10 to you.

[Terrible Shorts Depot]

Hey Lily Chen,
You're pretty cool for making this.

[cornfused]

Nifty.

[David Riley]

Thanks!

[Siverus Snape]

This is pretty cool stuff. One thing I noticed while I was playing around with it: at least on the mACF scoresheet (the only one I've looked at) if the tossup goes dead and bonus points are still awarded to one team, the spreadsheet doesn't flag it.

[Captain Sinico]

I had everyone use these in practice and they are really excellent. Thanks!

MaS

[rjaguar3]

I've tinkered with the mACF to make it suitable for Illinois format. This supports 5-point tossups (blurt rule), 3-, 4-, and 5-part bonuses, bouncebacks, and a tiebreaker tossup+bonus. The file should be attached.

[lchen]

Thanks for the comments!

if the tossup goes dead and bonus points are still awarded to one team, the spreadsheet doesn't flag it.

Thanks for catching that. I went back and redid the error detection, so it should work now. The new version is up on our team's website.

[dtaylor4]

As someone entering stats from such scoresheets, one suggestion:

Instead of having a ginormous box with total tossup points and small boxes for bonus points and BC, have three equally sized boxes for total tossups, bonus points, and BC. This way, the person entering stats can double-check for any entry errors.

[Ben Dillon]

I used the NAQT scoresheet at a tournament today to display the scores for the two teams; they appreciated having it up there on the big screen.

There is a bug, though. For the right-hand team, the Question Total and Cumulative Score doesn't work. The Final Score displayed at the bottom is correct, but the running score isn't because the first question doesn't register correctly.

[millionwaves]

We pretty much ran IO using these today, and they worked fantastically. Thanks a ton for creating these, they're super useful.

[cornfused]

Tryg - did you run this and the questions all on the screen? How'd it work switching back and forth?

I think I'll be using these for the upcoming LU tournament.

[Mike Bentley]

Creating some code to extract data from these spreadsheets and enter them into SQBS automatically might be something I look into doing in the future.

[Ben Dillon]

Thought of something else... Slimming down the columns so that they automatically fit on one 8.5 x 11 would be nice.

[johnnyfulcrum]

.

User was permanently banned for being enok walker avoiding a previous ban.

IP address was banned for a month.

[Grams's Go-Go Boots]

Go away, enok walker.

[lchen]

Hey, thanks for all the suggestions. I've put up an updated version with some bugfixes and minor aesthetic changes.

- fixed the bug in the NAQT version that displayed the cumulative scores incorrectly (Thanks, Ben.)
- slimmed down the columns so that they fit on one letter-sized sheet of paper (The answers/notes column would print on a second page, but you can just print the first page if you only want the scoresheet part.)
- made the bonus conversion cells larger

[Captain Sinico]

These are great and I want to thank you for making them; we put them to extensive and very successful use at Illinois Open this weekend. The one thing I'd request is that you add a column for the room the round was read in.

MaS

[cornfused]

These are great and I want to thank you for making them; we put them to extensive and very successful use at Illinois Open this weekend. The one thing I'd request is that you add a column for the room the round was read in.

MaS

Mike - how did you get the scoresheets from moderator to stat room - did you have them copy down the important parts onto actual paper, send it over wireless, or something else?

[jonah]

We emailed them to Donald.

[lchen]

You're welcome! I'm glad people like them.

The one thing I'd request is that you add a column for the room the round was read in.

That's a good idea. There will be a new version up shortly (2.46) with a cell for the room number.

how did you get the scoresheets from moderator to stat room - did you have them copy down the important parts onto actual paper, send it over wireless, or something else?

We considered using these for Prison Bowl 09 and one of the ideas was to have the moderators print them to a networked printer in the control room.

[Captain Sinico]

Hey,
Yeah, we have pretty ubiquitous, reliable wireless coverage, so we had people sent them by e-mail or over AIM/Google Talk/whatever (I opted for the latter.) Previously, we'd transmitted the relevant results from the paper scoring over e-mail or AIM, but this makes the transmission easier: the moderator doesn't even need to type the couple lines; just attach the file to whatever. I think the main benefit, though, it that it makes actually keeping score much, much easier and more idiot-proof.
I suppose if you lack internet connectivity, the sheets could be transferred on rotating memory sticks, or the stats needed for the reports (final score; q's heard; tu,neg, & q's hears for each player) just written down on paper or whatever with the electronic sheets saved for later records. Or I suppose one could even print the sheet and bring it to the stat room if one's got the printers... but that seems pretty impractical.

MaS

[Sir Thopas]

Also, Taft allows each moderator to input the stats locally, should the money be willing to be forked over.

[BuzzerZen]

should the money be willing to be forked over

You're seriously attributing propositional attitudes to money?

[cornfused]

This is great - we have no printer in the tournament building, but we DO have ubiquitous wireless access and plenty of laptops... so we're going to use Lily's program and email and physically bring my printer from my dorm room to the stat room.

No printer access, no problem - paperless tournament.

[Sir Thopas]

should the money be willing to be forked over

You're seriously attributing propositional attitudes to money?

It greases the palm, after all.

[dtaylor4]

This is great - we have no printer in the tournament building, but we DO have ubiquitous wireless access and plenty of laptops... so we're going to use Lily's program and email and physically bring my printer from my dorm room to the stat room.

No printer access, no problem - paperless tournament.

Are you counting the paper used to print and post stats?

For IO, this is effectively what was done. In the middle of a round, I would email the next round, and readers would email me the excel file.

[Mike Bentley]

I would probably advise people relying on wireless to have a backup plan. Wireless, especially on campuses, is notoriously unrelaible. The backup plan could be as simple as "bring all the laptop physically to the room where scorekeeping is done and have people just read out the scores for the rounds", but if you don't have such a plan in place you'll likely waste a lot of time trying to scramble for flash drives, etc.

[Captain Sinico]

...have a backup plan...

...have a backup plan...

...have a backup plan...

MaS

PS: Of course, your level of wireless reliability may vary, and so too the likelihood that your backup plan will need to come into play... but you can figure that out.

[cornfused]

We'll be hosting our tournament in Main Hall, the most dependable wireless spot on campus. But yeah, I'll have paper scoresheets available for in case that fails.

I think instead of emailing the questions, I will start the moderators out with the questions and then email the passwords - could anyone give me a quick rundown of how to password question files?

[cornfused]

OK, I found Mike Bentley's program, but I suppose that means I need to make sure everybody has WinZip. Still, I should be fine.

[cornfused]

Okay, how do I unzip .zip files when WinZip is no longer working (it refuses to work until I pay)?

[Red-necked Phalarope]

WinRAR.

[Siverus Snape]

Much better, freer, and less bloated than WinRAR: http://www.7-zip.org/

[Red-necked Phalarope]

Much better, freer, and less bloated than WinRAR: http://www.7-zip.org/

They're making you pay for WinRAR now? I didn't even check. Yeah, go with 7-Zip.

[Matthew D]

Just wanted to say thanks for creating the spreadsheets.... it has cut down my practice scoresheet printing to nothing.. now I am attempting to figure out how to use it at my tournament in Jan

[powerplant]

So I tinkered with the PACE sheet and made one for the KAAC Quick Recall format. When a tossup is answered, the question below it turns blue, indicating that it was a bonus. If people have any questions about it or suggestions for how I can make it better, I'd love to hear them.

Link

[lchen]

I've updated the scoresheets to version 2.6, which now supports the new 2010 PACE format. In addition, I've added highlighting for stolen bonus points, and correct calculation of % bonus/steals for the PACE format. From now on, the spreadsheet for the old PACE format will no longer be maintained.

[First Chairman]

I've updated the scoresheets to version 2.6, which now supports the new 2010 PACE format. In addition, I've added highlighting for stolen bonus points, and correct calculation of % bonus/steals for the PACE format. From now on, the spreadsheet for the old PACE format will no longer be maintained.

I appreciate your efforts on this, Lily.

[jonah]

I hate to look a gift-Lily in the mouth, but is there any chance of making a version of the mACF sheet that supports bouncebacks? The NSC-format one doesn't work perfectly for this because it has powers worth 20 instead of 15. It would also be great for there to be a cell at the bottom displaying the total number of bounceback opportunities (number of tossups the opponent got minus the number of bonuses they thirtied) to save a little time when entering data into SQBS.

[Frater Taciturnus]

I hate to look a gift-Lily in the mouth, but is there any chance of making a version of the mACF sheet that supports bouncebacks? The NSC-format one doesn't work perfectly for this because it has powers worth 20 instead of 15. It would also be great for there to be a cell at the bottom displaying the total number of bounceback opportunities (number of tossups the opponent got minus the number of bonuses they thirtied) to save a little time when entering data into SQBS.

Can't you just change the numbers?

[jonah]

I hate to look a gift-Lily in the mouth, but is there any chance of making a version of the mACF sheet that supports bouncebacks? The NSC-format one doesn't work perfectly for this because it has powers worth 20 instead of 15. It would also be great for there to be a cell at the bottom displaying the total number of bounceback opportunities (number of tossups the opponent got minus the number of bonuses they thirtied) to save a little time when entering data into SQBS.

Can't you just change the numbers?

Yes, but I thought other people might find the same thing useful, so it would be great if they were centrally distributed.

edit: Also, implementing the second thing I requested would require a lot of unraveling of the hidden columns (T-AF) that I don't understand at a glance, whereas Lily probably knows what they're doing pretty easily and would be able to do it much quicker.

[lchen]

I hate to look a gift-Lily in the mouth, but is there any chance of making a version of the mACF sheet that supports bouncebacks? The NSC-format one doesn't work perfectly for this because it has powers worth 20 instead of 15. It would also be great for there to be a cell at the bottom displaying the total number of bounceback opportunities (number of tossups the opponent got minus the number of bonuses they thirtied) to save a little time when entering data into SQBS.

Done and uploaded. I modified the NSC format spreadsheet to support 15s and -5s. I wasn't able to do the second thing; maybe I'll get around to it sometime later. Bouncebacks should be highlighted in blue, so it shouldn't be that much harder to just count them. There is a cell at the bottom that calculates the percentage of available bounceback points a team actually earns.

[huff paste]

It would also be great for there to be a cell at the bottom displaying the total number of bounceback opportunities (number of tossups the opponent got minus the number of bonuses they thirtied) to save a little time when entering data into SQBS.

Wouldn't they just be cells AA24 and AB24?

[jonah]

It would also be great for there to be a cell at the bottom displaying the total number of bounceback opportunities (number of tossups the opponent got minus the number of bonuses they thirtied) to save a little time when entering data into SQBS.

Wouldn't they just be cells AA24 and AB24?

Nope. That appears to be the total number of rebounded points (also displayed at H30 and Q30), but isn't the number of opportunities.

[huff paste]

It would also be great for there to be a cell at the bottom displaying the total number of bounceback opportunities (number of tossups the opponent got minus the number of bonuses they thirtied) to save a little time when entering data into SQBS.

Wouldn't they just be cells AA24 and AB24?

Nope. That appears to be the total number of rebounded points (also displayed at H30 and Q30), but isn't the number of opportunities.

That's rebounded points available, which if you divide by 10, is number of opportunities.

[jonpin]

Not to steal Lily's thunder, but last spring I designed several Excel '07 spreadsheets that serve this purpose. I hadn't really gotten a chance to continue development until recently (because they were saved on the school computers), but I now present these four versions of my spreadsheets for public consumption:

• ACF: Strictly speaking, mACF. Supports powers and negs. Also used for most regular season NAQT tournaments (20 tossups with sudden death OT)
• ACF bounceback: As above, but supports bouncebacks.
• NAQT official: Supports powers, negs, games of up to 26 tossups, and 3-tossup overtimes.
• PACE NSC: Supports 20-point powers and bouncebacks, as well as recording of 0-point wrong answers (no negs).

Both bounceback formats include a box at the bottom indicating how many bounceback opportunities there were (as well as a line in the footer telling the scorekeeper how to calculate it if keeping score on paper)

I believe these serve well under many situations:
1. You can print them out and use them as regular scoresheets. Everything is then pretty straight-forward.
2. You can distribute them to moderators electronically and they can use them on the computer, and then email the TD with the stat lines.
3. You can distribute them to moderators electronically and they can use them on the computer, and then print out a nice-looking scoresheet to bring to headquarters.

I find each of these options has benefits. The utility of option 3 may be the hardest to see, but as someone who has had to decipher incomprehensible handwriting of people who failed to add up scoresheets correctly, it was the choice I would've used at BCA's recent tournament, had I been able to guarantee computer access in each room. These scoresheets then give the option of planning to run a paperless tournament, but if the wireless network then crashes, you can print out each scoresheet, and you can also print out blanks for spectator/player/non-computer-using-scorekeeper.

When used on the computer, they perform several layers of error-checking. I invite/ask people to download these and play around with them to make sure all potential errors are caught (the exception being that awarding bonus points in the wrong column might not be flagged, simply because I think you're smart enough not to enter data in a solid black box).

Without further ado, here are my scoresheet spreadsheets. If you have any questions or spot any errors, just talk to me here, in IRC, or via email.

[jonpin]

Here's the PACE NSC version, since I'm limited to 3 attachments per post

[Down and out in Quintana Roo]

This is awesome. I plan on using these in practice more to help facilitate individual and (more importantly) PPB stats.