Monday, December 08, 2014

2014-15 College Football Bowl Pool

You've all been waiting very patiently for me to update my annual college football bowl pool spreadsheets, and here they are!

There were all sorts of changes this year. Five all new bowl games, one discontinued bowl game, and a few renames. Plus, we're in our first year of the College Football Playoff era! That's right, we've got one game in the list for which we won't even know the teams playing until later on.

Here's the new bowl games:

  • The Camellia Bowl. This one will be played in Montgomery, Alabama between a Sun Belt Conference and Mid-American Conference teams. The bowl is owned by ESPN Regional Television.
  • The Miami Beach Bowl. This one will be played in Miami in Marlins Park. We always need more Florida bowl games, right?
  • The Boca Raton Bowl. This one will be played at Florida Atlantic University stadium in Boca Raton. It's also owned by ESPN Events, and features Mid-American conference teams until 2019.
  • The Popeyes Bowl. Officially the Popeye's Bahamas Bowl for its sponsor Popeye's Louisiana Kitchen, this one will be played in Nassau Bahamas and features Mid-American conference teams through 2016. This will be the first game played outside the U.S. since 2010 International Bowl in Toronto.
  • The Quick Lane Bowl. This bowl will be replacing the Little Caesars Pizza Bowl. This isn't merely a renaming, though. It's a whole new bowl organization. The Little Caesars Bowl is no more (for now).
Here's the renamess:
  • The former Kraft Fight Hunger Bowl is now called the Foster Farms Bowl.
  • The former Buffalo Wild Wings Bowl is now called the Cactus Bowl.
  • The former BBVA Compass Bowl is now called the Birmingham Bowl.
  • The BCS National Championship is now called the College Football Playoff (CFP) National Championship, as we enter the CFP era. Bye bye, BCS.
Here are the files:

For those unfamiliar with the two-file system, the idea is that pool managers can distribute the 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: Since I wasn't sure how you folks would be dealing with the fact that we don't currently know which teams will be playing in the CFP Championship, I've made it so that you can easily edit those teams later. Some of you may prefer to have everyone pick a winner for that game upfront, filling in the teams playing with their two picks for the initial two playoff games. Some of you may opt to have folks send in their picks for that game later, once the teams are known. It's up to you, I guess.

Another note: I don't have the final record for the Navy team, since they play 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.

Finally, 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.)

Enjoy, everyone!

33 comments:

Anonymous said...

Thanks Excel_Geek. I have a question about the Summary Form and the Playoff game, Row 42. If we ask our pool to fill out who they think will win and copy that to the Summary form, it will not allow an entry other than what is placed in F42 and G42. Is there a way you can unlock user entries in row 42 to incorporate 1 of the 4 possible teams?

Andrew V said...

Thanks Excel_Geek. I have a question on how the Summary file incorporates the Playoff selections. If I have my group choose which teams they think will win, there is a possibility the entry will not be allowed. For example, if Alabama and Oregon make it to the Playoff Game, but one user chooses Florida State to win all, how is that accounted for in the Summary Form? In its current version of the Summary form, users can only select whichever teams are listed in F42 and G42. Can you somehow allow the spreadsheet to incorporate all 4 possibilities?

Andrew Vissotski said...

Thanks Excel_Geek. I have a question on how the Summary file incorporates the Playoff selections. If I have my group choose which teams they think will win, there is a possibility the entry will not be allowed. For example, if Alabama and Oregon make it to the Playoff Game, but one user chooses Florida State to win all, how is that accounted for in the Summary Form? In its current version of the Summary form, users can only select whichever teams are listed in F42 and G42. Can you somehow allow the spreadsheet to incorporate all 4 possibilities?

Excel_Geek said...

Great questions, Anon and Andrew.

If you have your participants change the team names in the CFP Championship in their entry forms and then you simply copy and paste special as values their picks and points into the Summary, the data validation won't be a problem. Sure, if you try to manually select each one, that wouldn't work due to the data validation.

Kaleb Christoffersen said...

How do we go about extending the number of participants in the summary sheet? For instance, using the autofill property of excel to drag across and add more participants (>40). Can you unprotect the sheet? Or, as a suggestion if you're writing vba, code such that the user can input the total number of participants into a cell and then populate the sheet based on the input?

Charles G said...

My thought was to lock the cells for the championship game (F41 & H41) on the entry form and autopopulate them based on the picks participants made on the first round games by using an =IF(ISBLANK(I33),"",I33) & =IF(ISBLANK(I34),"",I34) respectively. But you still run into the problem of assigning a point value to a game some members didn't pick the correct teams for. It seems the only solution is having contestants submit their winner only after the game is decided who's playing but having already assigned a point value to it. Anyone see a way around this?

Excel_Geek said...

@Kaleb, yeah, it's just not designed for easily adding more participants. You can unprotect the sheet and workbook -- it's not password protected. It is a bit complicated to add more participants, though.

Jeff Garl said...

Is there a way to customize the sheets to add a spread column, if we want to add a little more difficulty to the pool?

Anonymous said...

12/10 3:30p ET, unable to download the summary sheet...? Tried different web browsers.

Excel_Geek said...

@Jeff, feel free to unprotect the worksheet and workbook (neither is password protected) and give it a shot. I haven't added that feature yet, but maybe next year if enough people want that I will. I simply get too many requests to add this and that to do them all.

Excel_Geek said...

@anon who can't seem to download,

keep trying I guess. No one else is apparently having an issue. It's a large file (>3MB) so maybe it's just going to take awhile?

Unknown said...

We are trying to do our own version of this and cannot figure out how to get the conditional color formatting to happen/make it as easy as yours. I just stumbled upon your sheets and could use that, but we don't use confidence points. Any help for figuring this out? Thanks!

Excel_Geek said...

@Unknown, are you trying to modify mine or create your own from the ground up? If it's the latter, feel free to shoot me a copy [ e hun zeker @ gM ail dot com] and maybe i can give you some help or a pointer or two.

DJF said...

Hello - I love the bowl pool but am frantic because it won't download. When I click the link, a new tab and web page tries to open and no luck. Uploaded the other items on the page fine, so not sure what I am doing wrong.

Excel_Geek said...

@DJF, sorry you're having trouble with the download. Thanks for emailing me. I've sent you both files.

Anonymous said...

This is great – thanks for putting this together!

Tim P said...

Thanks Excel_Geek. I've chosen to assign a 40 point value to the NC game. How can I make the point option in column J for all other game 1 thru 38 instead of 39?

Excel_Geek said...

@Tim P,

Here's what you do:

Unprotect the worksheet (it's not password protected).

Show the column and row headers and unhide Column A.

The numbers in this column drive what point values can be assigned. Simply change the CFP Championship to 40.

Rehide column.

Rehide headings.

Reprotect sheet (to prevent yourself from accidentally breaking stuff).

Done.

Tim P said...

Done. Thanks. Another ?: I see the group chart as a type of leaderboard. Do you have a standard leaderboard with the leader on top showing the points earned and points still possible so players can track where they stand?

Excel_Geek said...

@Tim P,

I've never made such a thing. Feel free to knock it out. I'd love to see what your imagining.

Tim P said...

I'll attempt to put something together and email you the spreadsheet. Thanks again!

Rustler said...

I'm having the same problem that DJF just had..".When I click the link, a new tab and web page tries to open and no luck. Uploaded the other items on the page fine, so not sure what I am doing wrong".

Love your pool!

Can you help excel geek?

Excel_Geek said...

@Rustler,

Feel free to email me [ e hun zeker @ gM ail dot com] and i'll email you that file.

Caswell said...

First off, new to your site and a total novice at spreadsheets, but thank you so much for this. I've been scouring reddit and google for this exact concept and this is above and beyond what I'm looking for.

I'm having a problem opening the bowl summary file in one drive. The entry sheet opens just fine but the other isn't supported. Also I know you're the excel guy, but the xls will open in google drive, but the values all seem to be off by a column or two... any idea how to possibly fix that? My pool users prefer to use google docs since they all have it on their phones.

Thanks again and terrific job!

Chuck said...

Where is the summary chart data for the group chart. I want to re-select the data for the chart...thanks!

Excel_Geek said...

@Chuck,

It's in a hidden worksheet. Simply unprotect the workbook and unhide it.

Jon said...

loved υѕιng тнιѕ ғor college pιcĸѕ. тнanĸ yoυ very мυcн ғor pυттιng ιт тogeтнer. wιll yoυ вe doιng a мarcн мadneѕѕ ғιle aѕ well?

Excel_Geek said...

@Jon, yes. Look for it later today or tomorrow.

Anonymous said...

Will you be doing one for the 2015-16 lineup soon? Our office pool loves your sheets!!!

Excel_Geek said...

Yeah, it's coming soon. I've been really swamped, so i didn't get to it last night after Selection Sunday.

Anonymous said...

You da man! Cant wait

Scott Erickson said...

Excel Geek, are you putting together a 2015-16 college bowl pickem? I love using it with my friends. Just want to send it out to them and give them enough time to make the picks. Thanks

Excel_Geek said...

Yes. It's coming.