Saturday, December 12, 2015

2015-16 NCAA Football Bowl Pool Files Are Ready

UPDATE as of 12/14/2015: Initially, I left off one of this year's now 42 bowl games -- actually the very first one: the Air Force Reserve Celebration Bowl, featuring Alcorn State and North Carolina A&T. Thank you, readers "Anonymous" and Tron Guy, for setting me straight. For some reason this bowl doesn't appear on the NCAA's website, nor on Wikipedia. And none of the betting line service websites I looked at to get odds shows it. Odd. This has been corrected in both files now.

UPDATE as of 12/28/2015: I've now got a version of the summary file out there with the aforementioned scenario analysis built in. I'm using the betting lines (point spreads) to determine the probabilities of the favorites winning, and then running 1,000 random scenarios, where the outcomes are weighted accordingly. Then I apply the winning teams from each scenario to each participant's picks and points, and see who wins each scenario. Then I calculate each participant's overall odds of winning the pool, based on that sample of 1,000 scenarios. Finally, I do some analysis for each participant to highlight which games are of particular importance to each person. For example, it might be that for a given participant, 100% of the scenarios for which they win the pool involve California beating Air Force in the Armed Forces bowl. It'd be handy for everyone to know that. It's worth noting that running these 1,000 scenarios on my PC -- which is a decent Dell Latitude E6520 (2.40 GHz Intel i7 CPU with 8 GB of RAM) -- takes a touch over a minute. I do think that only running 1,000 scenarios is likely way under-sampling things, particularly when there are 20+ games remaining, which means there are over a million and up to 4.4 trillion possible outcomes! Given that, I could build out an option to run 10,000, 20,000, or even more scenarios. It's just that run time for such an option will go up in a linear fashion as you add more scenarios, meaning for 20,000 scenarios it could be half an hour or longer probably, and I don't know that folks really want to wait that long for what still amounts to a sampling of potential outcomes. Also, I don't know that the overall precision of the estimated odds to win for each participant will become that much better.

At long last, here are this year's college football bowl pool management files:

It took me a bit longer than normal to get the files out this season, and I've gotten many comments, emails, Facebook messages, etc., asking when they'd be ready. Well...they're ready.

For those unfamiliar with the two-file system, the idea is that pool managers can distribute the simple, single person Entry Form version to those in their pool to make their picks and assign confidence points. The participants them email back that file, and the manager can simply copy, paste special... values those picks and points into the Summary file, which is where they manage everything.

Note: Here's how I dealt with the playoff semi-finals and championship: I've used formulas to automatically fill in the two winners of the semifinals for the left part of the summary file. For each person's picks, they'll have the two teams they selected to win those semi-finals to pick from in the championship. Let me know if that doesn't work well or if you're confused by it. It does make the Individual Chart for the championship game a bit odd if a participant picked a team to win that game that didn't even play in it, but the points system still works fine.

Another note: I don't have the final record for the Navy team, since they're currently playing in the annual Army-Navy game this weekend. I've also left that cell unprotected so you can easily update the record for the Midshipmen if you want to.

Also, I don't yet have my personal picks completed, but will add them, as well as those for my wife (MRS. EXCEL_GEEK) and son (EXCEL_GEEK, JR.) and update the files once we're ready. Some of you have voiced a preference to have those picks included, to give you a benchmark for your own picks (and to see me lose each year to my wife.) UPDATE: Our picks are now included.

Finally, I'm planning on working in a new feature that I'll release in a few days that will enable the pool manager to run a series of simulated scenarios to see which Participants, given their picks and points vs. games that have already been played vs. point spreads, have the greatest chances of winning the pool. It should be a neat feature.

Enjoy, everyone!

38 comments:

Virginia Michaels said...

You are the best -- Our entire office looks forwarad to this every year! Thank you!

Anonymous said...

You left off the first bowl game. There are 42 bowl games this year. The first bowl is the Armed Forces Bowl on 12/19 featuring Alcorn St. vs. North Carolina A&T at noon eastern on ESPN.

Excel_Geek said...

@Anonymous, I think you may have your facts mixed up. There are 41 bowl games, including the championship game. The Armed Forces bowl this year is on the 29th between Cal and Air Force.

Tron Guy said...

I think @Anonymous is referring to the Air Force Reserve Celebration Bowl, which is between Alcorn State and North Carolina A&T on Dec 19. I did not find it on the entry form/summary.

Wayland Abernathy III said...

Great job as usual.

Excel_Geek said...

Huh...indeed, I've left off the Air Force Reserve Celebration Bowl. It didn't show up on the official NCAA website's list of bowls, nor on Wikipedia. I'll get it added, though.

Anonymous said...

Is using the sheet for a straight pick pool as simple as adjusting points won for each game to 1?

Excel_Geek said...

RE: Using for straight pick pool...yup, just enter a 1 for all games' confidence points. It'll highlight them all yellow -- this is a way for those using confidence points to know when they've entered duplicate values -- but it'll work just fine.

Garret said...

Really great work as always! Thanks for doing this each year!

Jonathan said...

Thanks for doing this. It's GREATLY appreciated!

Jonathan said...

Thanks for putting in the time to do this. It's GREATLY appreciated!

David Whitford said...

Please forgive my ignorance.
How does the point system work? is there an explanation for the users?

Excel_Geek said...

@David Whitford, no worries. The idea of the confidence points systems is that you allocated 1 - 42 (the number of games) points to each game, and that's what you get if the team you picked wins it. The games you're more confident in the outcomes, you give more points to. Some folks prefer to use this as a "straight picks" pool, and just allocate 1 point to each game, though. It's really up to you.

Anonymous said...

Forgive my ignorance - haven't played / used our pool since you started putting in points spread. Is the point spread a simple FYI or does the math impact an outcome i.e., if the "dog" loses by less than the spread does it now become a winner ?

Excel_Geek said...

Good question, @Anonymous RE: point spreads. Currently they ARE there just as an FYI. It doesn't impact the picks. They're still straight picks -- winner vs. loser. I put them in there to get ready for some future features -- one being the ability to pick vs. the spread vs. just winners and losers, and the second a feature to probablistically determine which participants are most likely to win, given the results of the games that are complete, the games remaining, and the point spreads of those games.

Kendall said...

For some reason, when I put entries in the summary sheet, the "GAME CHARTS" tab works correctly, but the "INDIVIDUAL CHARTS" and "GROUP CHART" tabs do not input any information, even if I pick winners. Am I doing something wrong here?

Excel_Geek said...

@Kendall, not sure. You can always email me your file, and I can take a look. I use gmail, and my email is my first initial and last name. Eric Hunzeker.

chris moseley said...

This is awesome!! Thank you!! I'm assuming if you don't want to use confidence points then you can simply put 1s instead...?

Excel_Geek said...

@Chris Moseley RE: Using for straight pick pool...yup, just enter a 1 for all games' confidence points. It'll highlight them all yellow -- this is a way for those using confidence points to know when they've entered duplicate values -- but it'll work just fine.

Tim O'Brien said...

Hi, great sheet! Only Q I have is: for the National Championship Game. Because the semi-finals are on the spread, and the Nat'l Championship has no line, our pool allows you to pick any of the 4 teams in the semis to win the Nat'l Championship since a team could win the semi game but fail to cover the spread, and thus you could pick a team that loses the game, but covers with the spread, and that team would not be eligible to be in the Nat'l Championship. Is there any way to change the command so that all 4 teams are eligible to be picked from rather than the 2 semi teams that the player chose with the spread?

David Whitford said...

@Kendall, mine is doing the same thing.

Tim O'Brien said...

Awesome spreadsheet! Quick Q about the National Championship picks. In our pool, we allow the players to pick any of the 4 teams from the semis. Because the Nat'l Championship has no spread (and no teams as of yet), you play a no-line pic'em from among the 4 teams. Tying the player's semi picks to the Nat'l Championship doesn't work for our pool because the player may pick a team based on the spread and that team may cover, but not get into the Nat'l Championship. Is there any way to unlock that command so that you can pick any of the 4 semi teams? Great job!

MikeyB said...

Is there a way to have the points be ONE and automatically fill in each participants
cell when you choose the winner, or do you have to manually put in the points with each game?

Excel_Geek said...

@Tim O'Brien, what you could do is unprotect the sheet (it's not password protected), and then change the data validation used for everyone's Championship game picks to reference the list of four teams in the semis. You'll have to create that list somewhere -- add a new tab is you like -- since Data Validation only likes the values to be in one column or row. Make sense?

Tim O'Brien said...

Yes, thanks very much!

PoolBoy said...

Great spreadsheet! I'm going to have players pick against the spread for their picks. How would you recommend handling a push? Just count it as zero? Another option would be to give everyone half of their wager?

What do you all think? Also wondering how to handle the selection using the spreadsheet in the event of a push against the spread.

Excel_Geek said...

@PoolBoy, cool use of the spreadsheet, though I think it'd take a few modifications to handle pushes. Maybe that's something I'll consider adding support for next year. For this year, you can always make any tweaks you like. The sheets are protected, but not with a password.

Tony said...

This is great stuff! Thanks so much! I'll be donating for sure. AND you saved me from having to track and IT guy down to help me out.

Quick question: I need to add 5-6 participants (total pool is up to 46 participants now). How do I do that without losing the formula? Thanks much!

Best,
Tony

Excel_Geek said...

@Tony, I'm glad you like it. Adding more participants isn't super easy. You might be better off just having two files if you're not proficient in Excel. Certainly that's not ideal either, but it could get you through. If you want to dig into it, though, unprotect the Picks sheet and make the column/row headers visible again. Then head all the way to the 40th participant and unhide the three columns to the right of the two columns that are visible. You'll need to copy those five columns and paste them to the right of the last one. Then you'll have to unhide a sheet called summary_chart_data and make some modifications there. Then, if you want them updated, you'll have to dig into the Game Charts and Individual Charts sheets. Again, not super straightforward. I'd recommend just having some folks 41-46 in a second file.

Anonymous said...

On the GAME CHARTS sheet, what is the Bowl Ranking calculation? Mine's showing the Div/0 error on all.

Excel_Geek said...

@Anonymous RE: Bowl Ranking...that tries to represent which bowls, from a relative perspective, have the most confidence points being wagered on them.

Steve said...

Is there a straight up version of the Bowl Pool?

Excel_Geek said...

@Steve, read the rest of the comments, too, but you can just enter 1 in the points for every game if you want. Also, the point spreads are just informational.

Anonymous said...

Any update on whether scenarios will be put in the spreadsheet?

Excel_Geek said...

@Anonymous RE: scenarios...

I'm working on it. It's sort of a complicated thing to try to implement AND try to keep the file size manageable. I'll keep you posted.

Joe said...

Super Awesome Sheet! I thought I was an excel geek, I bow down to your skills!

I will be running the office pool starting next year and am moving from their old system of pen and paper to show them the way of spreadsheets and shared updates. I have a couple of requests and would be willing to pay for a custom sheet if needed, let me know if that is the best route.

1. Straight Pick 'em, figured out the "1" in each column. Would like to add a line for total score tiebreaker on the second to last or last bowl game. User picks a total score for the game and closest to the score breaks the tie.

2. As previously requested, an easy way to add more than 40 users.

3. Ability to sort the users on the sheet alphabetically or by top score.

4. Put the sheet on google docs or smartsheet so there is an easy way to share the data with users "real time". I tried with both and it doesn't look like this will work due to the complicated features/formulas being used and not yet supported by them. Maybe a pipe dream. I haven't played with Excel 2016's sharing yet so not sure if it would work for this.

Again, awesome sheet, if you do nothing else it will be so much easier than their old system. Thanks!!!

Joe said...

Super Awesome Sheet! I thought I was an excel geek, I bow down to your skills!

I will be running the office pool starting next year and am moving from their old system of pen and paper to show them the way of spreadsheets and shared updates. I have a couple of requests and would be willing to pay for a custom sheet if needed, let me know if that is the best route.

1. Straight Pick 'em, figured out the "1" in each column. Would like to add a line for total score tiebreaker on the second to last or last bowl game. User picks a total score for the game and closest to the score breaks the tie.

2. As previously requested, an easy way to add more than 40 users.

3. Ability to sort the users on the sheet alphabetically or by top score.

4. Put the sheet on google docs or smartsheet so there is an easy way to share the data with users "real time". I tried with both and it doesn't look like this will work due to the complicated features/formulas being used and not yet supported by them. Maybe a pipe dream. I haven't played with Excel 2016's sharing yet so not sure if it would work for this.

5. Also when doing a straight pick 'em, changing the scale of the group chart to be closer to the total number of bowl games.

Again, awesome sheet, if you do nothing else it will be so much easier than their old system. Thanks!!!

Excel_Geek said...

Wow, Joe, that's quite a list. Feel to reach out to me via email, too. 1a and 1b are pretty easy. 2 and 3 involve a total rework. 4 we'd have to talk about. If sharing the file easy is what you're after, I have some ideas. If you want this to BE a Google spreadsheet, you're crazy. :)