Monday, December 05, 2016

2016-17 College Bowl Pool Sheets

UPDATE 12-06-2016: A few of you have reported that there's a bug in the way you set picks for the National Championship game. I hadn't moved the data validation from last year's semi-final games to this year's. That issue is now fixed in both files. I apologize for the sloppiness.

UPDATE 12-28-2016: I've fixed a couple more issues in the Summary file. The first dealt with the way in which the was with the Bowl Rankings and Average Wagers are calculated in the GAME CHARTS sheet. I had missed one of the games, and it cause the rest of them to be referencing the wrong data. The second was on the CRITICAL GAMES tab. I had the National Championship game referencing the wrong possible teams, since the playoff games change each year. This same issue was impacting how the ODDS OF WINNING scenarios were being ran in the hidden scenario data tab. Basically, it was only picking from Nebraska, Tennessee, South Alabama, and Air Force as the possible teams in the championship game instead of from Washington or Alabama in the Peach Bowl or Ohio State or Clemson in the Fiesta Bowl. That is now fixed, as well. Thank you, Matt, for helping identify some of these issues.


Ok, everybody! This season's college football bowl pool files are now ready! Many of you have been patiently (and not so patiently) nudging me to get these ready. I'm like, "Hey, can't we at least let the Army-Navy game happen first, so we know what their regular season records are, at least?" and you all are saying, "Nope." Fine. Here you go.

Some noteworthy items:

  • Since we don't know Army and Navy's final records, I've left those two cells in both the Summary and Entry sheets unlocked, so that they can be easily edited to specify their respective final records. Note that if you make picks, copy-paste picks in from Entry Forms, etc., before making that edit, your picks will no longer match with the team names/records and scoring will not work for those games.
  • The bowl line-up is exactly the same as last year, with the exception that the GoDaddy Bowl has become the Dollar General Bowl.
  • There aren't currently betting lines set on all of the games, so I just added the spreads for those already available -- check Vegas Insider or similar sites. I did, though, use the first team as the "favorite" for all other games, so that I could do some testing with the "Odds of Winning" functionality, which doesn't work unless favorites are set. This is interesting to know, so that if you find a game has no favorite, at least pick one of the teams and either leave the Spread blank or set it to zero.
  • My wife, son, and I haven't had a chance to make our picks yet, so for those of you who like to use our picks as benchmarks of sorts, you'll have to wait until I update that later.
  • I did choose to keep the FCS-level Celebration Bowl as part of the line-up. Feel free to have your participants make picks for that game or not, as you like. I think that's likely to be a great game, btw.
Without further ado...the files:
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.

As always, the file is protected, but not with a password, so feel free to unprotect the workbook and then unhide some of the sheets running this stuff behind the scenes to see how it works. And if you add some cool hacks/additions, pass them along, so we can all enjoy them.

Final, as also always happens each year, several folks, as they were pinging, pestering, checking-in with me offered to pay me or donate to me for the sheets. This, like the NFL pick 'em sheet I do, I do for free as a labor of love. That said, if you are so compelled, feel free to PayPal me whatever you like:

$5.00     $10.00     $25.00     $50.00

Really you just have to change that dollar amount in the URL -- https://paypal.me/excelgeek/50.

Enjoy, everyone!

Wednesday, August 10, 2016

2016-17 NFL Pick 'Em Pool

Folks, I've completed and release this season's NFL Pick 'Em pool. Like past years, many of you have diligently been pestering me for weeks now. Keep doing that! It's paying off. I'm actually getting this out well before the season, unlike last year. Now, I didn't have the ambitious list of new features this year that I did last year, but I did have to contend with the Rams moving back to LA, which actually ended up being more involved to change than I'd like to admit. Also, at the request of several pool admins, I did create a companion "Picks" sheet to distribute to pool participants, so they can simply email back the file for the admins to copy-paste into the master sheet. Just remember to always copy, paste special... values when moving their picks into the master sheet, so as to not jack up formatting or other stuff.

UPDATE: I guess since I was able to get this year's version out so early compared to years past, I've been compelled to throw in one last feature enhancement. Many of you through the years have been asking for an automatic game results/score updater, using data from the web. I've got that feature now implemented, pulling scores from my score information website of choice -- footballdb.com. Please let me know if you encounter any issues using this new feature. That is all.

Without further ado, here are the files:

Also as happens each year, several folks, as they were pinging, pestering, checking-in with me offered to pay me or donate to me for the sheet. This, like the college bowl pool sheet I do, I do for free. That said, if you are so compelled, feel free to PayPal me whatever you like:

$5.00     $10.00     $25.00     $50.00

Really you just have to change that dollar amount in the URL -- https://paypal.me/excelgeek/50.

Anyways...please enjoy this year's NFL Pick 'Em. If you encounter a bug or need help with using the sheet, let me know.

Later,

Excel_Geek

Sunday, March 13, 2016

2016 March Madness Files Are Ready!

UPDATE: I've updated the Participant file ("Individual Picks") after several of you reported a bug that prevented you from using the Pool Setup & Results Summary functionality due to a "Run Time Error 1004" issue dealing with a cell being protected. I've addressed that issue, which was a problem in the Participant sheet. That's the good news. It's fixed. The not great news is that if you're a pool admin, you'll need to get all of your pool participants' picks into this updated sheet for that function to work. I'm truly sorry about that.

Here we go, everyone! The much anticipated ExcelGeek.com March Madness pool manager spreadsheet (and associated individual picks spreadsheet) is out!

The system works the same as in previous years. There are two files: The first is the "master" spreadsheet, used by the pool organizer to track the results of everyone's picks vs. who actually wins, etc. This is where you determine how many points a correct pick in each of the rounds is worth, as well. Also, the pool manager can make his or her picks in this file, too. The second file is the simplified "individual picks" spreadsheet used by the participants in the pool to make their picks and send the file to the pool organizer to automatically pull their picks into the master.


Once again, the individual picks spreadsheet is free, but the more complex "master" spreadsheet is not. It is ONLY $2.00, though. The files are both locked down and protected. In the case of the master spreadsheet, it requires a "key code" to unlock the magic.

Like I first did in 2012, I won't make you email me the "lock code" the file presents to you when you first open it and then email you back a "key code" to unlock it -- I've automated the process.

What you will do is the following:
  1. Download the "master" file by clicking on the link above.
  2. Open the file. Make sure you've got macros enabled. If you didn't have macros enabled when you first opened the file, enable them, then close and reopen the file, making sure to enable macros. If the file doesn't display a "Lock Code" when you open it, something didn't work right. Try again.
  3. Copy the "lock code" the file presents you.
  4. Paste that code (be sure to delete any trailing spaces added when you paste it -- it normally adds an extra trailing space for some reason) into the "Lock Code" field below.
  5. Click the "Buy Now" button below to pay the $2.00 for the file via PayPal. You do not need to have a PayPal account to pay this way. Any major credit card will work just fine.
  6. At the end of the transaction, you'll be redirected to a confirmation page with my pretty little avatar on it. It will display (among other things) the "key code" you'll need to activate the file.
  7. Copy and paste that code into the spreadsheet into the proper place, click submit, accept my terms and conditions, and you're off and running!
Again, if you don't paste in your Lock Code and complete your transaction, you won't receive a "Key Code" that will actually work to unlock your file. Do not forget this part! You may have to pay another $2.00 if you mess it up.


Lock Code


Best of luck, everyone!

Excel_Geek