Monday, December 03, 2018

2018-19 College Bowl Pool Files Are Ready!

Ok, everybody! This season's college football bowl pool files are now ready!

Some noteworthy items:

  • I'm again not waiting until after the Army-Navy game, so I don't know Army's final record, so I've simply listed them as "Army (10-2 or 9-3)". I've found this to be simpler than leaving that cell unprotected, allowing you to edit it, and then dealing with the possibility that you do that AFTER picks are entered and thereby cause the picks for those game to no longer match.
  • The bowl line-up is essentially the same as last year -- no new bowls and no eliminated bowls.
  • We do have three sponsor/name changes: The Zaxby's Heart of Dallas Bowl is now the Servepro First Responder Bowl. The Motel 6 Cactus Bowl is now the Cheez-It Bowl. And the Foster Farms Bowl has become the Redbox Bowl. You can just feel the tradition building with those...
  • This year, in addition to including the teams' records, I've included their rankings (for those in the top 25).
  • I've updated the sheets with opening betting lines for your convenience, but you should probably update these as we get closer to the games. I use the "VI Consensus" lines from Vegas Insider.
  • Finally, 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. It ends up being an exciting game, at least.
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.

Finally, 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/5.

Enjoy, everyone!

38 comments:

Anonymous said...

I think you have an error with the championship game in your sheet.

Anonymous said...

I think you have an error in your championship game formula.

XXKINGKOBRAXX said...

The playoff games are wrong.

should be cotton and orange bowls not rose and sugar.

this is the best spreadsheet ive ever used though.

Anonymous said...

The spreadsheet has the national championship between the Rose and the Sugar,it should be between the Cotton and the Orange. And the date should be 01/07/19 at 8:00 p.m.

Excel_Geek said...

Opps! I've corrected both the Picks and Summary files so that the proper games are included in the 4 team playoff. I'm not sure how I didn't catch that the first time. Trying to go too fast, I guess. I've also corrected the date and time of the game and formulas driving the participants of the National Championship game.

Let me know what other issues you may find!

Anonymous said...

Line 41 where you make your pick for the National Champion has no drop down choice to select from

Jill L. said...

Have you considered adding a sheet that lists all participants ranked by current points (highest to lowest) to make it easier to see who's ahead based on numerical values along with the group chart that shows it visually? I have some older participants that are not real computer savvy that just want a list and struggle to understand the sheet (or multiple tabs) with in the spreadsheet.

I have tried to add a sheet that does something similar.. but the code is FAR beyond what I know... and I usually end up messing the whole thing up. =;(

I do appreciate you making this available!

Unknown said...

What if i have more than 40 entries?

Excel_Geek said...

@Anonymous RE: Line 41 (in the Picks file)...

I'm not seeing the issue you're apparently seeing. Things appear to be working as designed. Feel free to send me your file or a screen cap of the issue. My name is Eric Hunzeker. My email is comprised of my first initial and last name and is from the main email service provided by Google.

Excel_Geek said...

@Jill...

Not a bad idea. Maybe that's something I'll add for next season.

Excel_Geek said...

@Unknown RE: more than 40 entries...

Sadly at this point you'll simply have to have more than one copy of the file running. Adding more participants is no trivial task AND the size of the file keeps getting bigger and bigger.

Anonymous said...

I am having the same problem with the pick column on Line 41. It is letting me select from who i pick in line 39 and 40, not from the 2 teams from the Orange and Cotton.


Anonymous said...

The drop down for the national championship is still showing the winners of the Rose and Sugar instead of the winners of Cotton and Orange. For example, I pick Clemson and Bama to win, and their names are loaded into the matchup cells for the N.C., but the drop down shows the teams I pick for the Rose and Sugar (ex. Georgia and Ohio St.)

Anonymous said...

I'm having the same problem..."Line 41 where you make your pick for the National Champion has no drop down choice to select from"

Anonymous said...

Hello. The drop down choices for the National Championship game is still set to the Rose and Sugar bowls vs. Cotton and Orange bowls

Excel_Geek said...

@All -- I've corrected the issue with making picks for the National Championship game in both the Picks and the Summary files. Sorry about that mistake. -- eric

Sarah said...

Thanks for the fix - but I still cannot make a selection in the championship game - says it is password protected for that box????

Excel_Geek said...

@Sarah...

Are you sure you're trying to make a selection or pick a favorite? I have the cell to pick the favorite locked down b/c we don't yet actually know who's in that game, so there are no favorites. The actual cell you make your pick in should be fine, right?

Sarah said...

good to go.
Thanks

Nick said...

Hello,

This is the third year that I have used your workbook to manage the college bowl pool. It is simply incredible. One question that I have is, one of my pools is just 10 people. Normally, as I have done for this year as well, I just go through and delete the columns in the first worksheet, the corresponding rows in the second, and the columns and rows in the third and 4th. I was curious if there is a simpler way to do this, perhaps in the hidden worksheets? There is no issue, and it is by no means a pressing concern, as I have gone through the main worksheets and deleted out 11-40 for each. I was just curious! Thank you for doing this yearly, I truly appreciate it!!

-Nick

Excel_Geek said...

@Nick...

Nah...I'd probably be doing just has you do if I wanted to cut down the clutter of all the unused participant slots. Sorry! It's tough to build a spreadsheet as complex as this readily sizeable for anyone's preferences. Most of the time I get requests to add 10, 20, 50 or 100 more!

Gino said...

Is it normal for the recalculation of winning odds to take so long? 25 mins estimated for me

Unknown said...

A couple things,

1. I don't think you fixed the problem with the national championship game on the critical games sheet. It still displays wash/osu/uga/tex

2. I would like to view the hidden sheets to see the formulas and I am not sure how to do so

GInny said...

We have 14 people playing in our bowl pick em (so 14 columns. When I enter the winners, only two of the columns/people have their "picks" change to green or red. What have I done wrong???

Excel_Geek said...

@Unknown RE: not fixing the national championship game on the Critical Games tab and viewing formulas...

I've fixed the formulas in the Critical Games sheet. Feel free to download a new copy of the Summary file, or feel free to fix the one you have yourself: Simply unprotect the sheet (it's not password protected), modify the formulas in the bottom four cells to point to the proper games (rows 34 and 35, columns D and F).

To view hidden sheets, simply unprotect the entire workbook -- again, not password protected -- and then unhide hidden sheets.

Excel_Geek said...

@Gino, yep, depending upon the computer (processor, RAM, etc.) you're using, it can easily take that long. I just launched that feature on my and it gave me an initial estimate of just under 20 minutes. It's a pretty intense process it must go through to calculate these odds.

Excel_Geek said...

@Ginny, I'm not sure what you've got going on. I'm not having the same issue. Can you screenshot what you've got and send it to me or even just send me your Summary file, and I can take a look? My name is Eric Hunzeker, and I use the main email provided by Google using my first initial and last name as the address.

XXKINGKOBRAXX said...

I have the same issue as Ginny. from entry 1-25, nothing changes, from then on they change(26+)

I'll send my file to you.

Excel_Geek said...

@Ginny (again) and XXKINGKOBRAXX, I'm guessing it may have been an issue where you may have copy-pasted, rather than copy-paste special... values the picks from the picks sheets to the summary sheet, thereby removing the conditional formatting from those cells. I can confirm if you send me a file. To fix, simply start again with a fresh (and working) summary file, then copy, paste-special...values the picks from your current file to the new one.

XXKINGKOBRAXX said...

Thats exactly what i did. Ill start over.

thanks,

Tom S said...

I kept getting an error running the Odds of Winning calculations and think the following easy fixes helped.

BUT - I entered in the words "pick 'em" in the odds columns for teams with even odds. This was THE crashing error, I think. Entering in 0 or leaving it blanks works best. (I discovered this once I looked at the "lines_odds_data" worksheet.)

Worksheet 'scenario_data' - pointed cell AT11 to the Orange and Cotton victors from AR11 & AS11 to cells AG11 & AH11, respectively.

Using your offset data, I created an easy to read Ranking list showing who's in first with columns for the current score and points possible. Here is the raw list of my first four participants (this is unranked data). After this, I rank and sort. The ranking is NOT automatic.

PLAYER Pts PtsPoss Champ ---offset data----
1 25 843 #1 Alabama (13-0) 1 10 11
2 155 853 #1 Alabama (13-0) 2 15 16
3 53 859 #3 Notre Dame (12-0) 3 20 21
4 135 813 #1 Alabama (13-0) 4 25 26

If you are interested in the Ranking worksheet, I can pretty it up and share, unless you've already come up with something.

T

Excel_Geek said...

@Tom S, yeah, non-numeric values for the spreads would be problematic. I'll have to fix that wrong reference in the scenarios sheet. Feel free to share your work if you like. Sounds line you've done something cool.

GInny said...

You are correct, I did not use "paste special" I did correct the problem by using format painter. I simply copied one of the columns that ws working and copied it to the others.
My next question is how are you going to handle the First Responder Bowl since it was cancelled? I was going to award everyone the points they had placed on the game, but wsn't sure if this was the best way to handle it?

Unknown said...

How is everyone handling the cancelation of the SERVPRO First Responders Bowl between BC and Boise St.?

Excel_Geek said...

@Ginny & Unknown RE: The canceled First Responders Bowl...

It's kind of a philosophical question, really. Did your participants pick a team that they expected to win or did they pick a team they expected to not lose? If they picked winners, they'd get no points, since neither team won. If they picked not-losers, they'd get their points because neither team lost. Personally, I'm leaving this game with no winner and awarding no points.

Gino said...

For those wondering how I handled the First Responder Bowl, these picks are "confidence" picks. Therefore, if I placed a 19 on TCU, 20 on Boise State and a 21 on Washington State, I would be more confident that Washington State wins than TCU. Because the game was ruled a no contest, I wrote a little formula that decreased the amount of the only the points that were greater than the amount that person put on the First Responder Bowl by 1 each. As if the game didn't happen. I'd still be more confident in Wazzu over TCU, but this time without Boise in the middle.

Also, I've been trying to read the formula and figure it out but I still cant... what exactly is the "critical games" percentages measuring? I figured it was the percentage of times that team won on the sims that that participant won, but I don't think that's it after looking at the formula closer. Could you help me out here just understanding this?

Tom S said...

I've been looking at the 'Critical Games' worksheet, too. Unless a player is in the hunt to win it, the Critical Games worksheet isn't applicable. Do I have that correct?

The calculation returns "#DIV/0" and doesn't list %s for upcoming critical games (the last place player gets the shame bell rung at them). If I have it correct, the 'countifs' function is looking for a 1 in the denominator in the trial rankings section of the 'Scenario Data' worksheet. If a player isn't going to finish 1st in any trial, it defaults to and divides by 0.

~~~~~~
Update: I created a nice worksheet to add if you want to review. Automatically creates a ranked leader table (ranks players based on point total and then points remaining). I am still working it a bit...just need to know where to send it.

Tom S.

Excel_Geek said...

@Tom S and Gino RE: critical games tab...

Yeah, that's what it's essentially doing. In all of those 5000 trials that run, what percentage of them does each participant come in first when each team wins. And it doesn't work if the participant has no chance of getting first.

Tom, feel free to share to my gmail. It's first initial last name. Eric Hunzeker.