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.
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!
30 comments:
Pardon my ignorance, Geek, but this looks exactly like what I am looking for for some friendly teacher action at my high school.
I was playing around with the summary sheet to see how it works. Will the points show up and total automatically once the winner of the game is in? They did not while I was experimenting.
Thanks!!!
Laurie
@Laurie,
Yes, just start indicating who won and it'll work for you.
Mr. Excel Geek,
In our office pool we like to have everyone select the winners of the playoff games and the winner of the National Championship at the start. We think it adds a little more intrigue into the whole process. For some reason the sheet isn't letting me select of the National Championship after I pick winners for the Peach and Fiesta Bowls. Something I am doing wrong? Or an easy fix? Thanks
@Scott...
You've found a bug - something I didn't catch. I need to repoint the data validation for everyone's picks to the right cells. I'll get that fixed and posted.
Love these pools, and our group has been using them for several years now. Is there any way to easily expand these past 40 entries? Obviously, i am excel challenged or i would do this on my own. Last year, our pool had about 50 entries.
Thanks
@Doug RE: expanding number of participants...
Unfortunately, given the overall layout and design and complexity, adding more participants is a non-trivial effort. I don't plan on taking this on unless quite a few folks are asking for it. Hopefully having to use two or more copies of the file to manage your pool isn't a major headache for you.
I'm sorry, Excel Geek, and I have done this before, but when I enter in a few picks, and then select some fictitious winners, nothing happens. No points show up in the summary. The correct picks do turn green and the incorrect picks turn red. What am I doing wrong?
Thanks!
@Laurie RE: no points...
Have you allocated any points to the picks you made? If you left the Points column blank, it won't give you any points for correct picks. The idea is to enter 1 through 42, each number once, for each game to specify how many points you'd like to win if you get the pick correct. You'd allocate more points to games you are more confident of and less points for games that are toss-ups. If you don't run your pool like that and simply give the same number -- e.g. 1 point -- for each correct pick, then put 1s in the Points column. HTH
The AHA moment!
Thank you!
Hey man, you rock. I'm using your sheet again this year for my 50 person pool. I'll be sure to drop some money in your PayPal. Thanks again.
Awesome stuff man, Ive been running my on excel sheet but doesnt compare at all to this one. Only question I have is...On the main "picks" tab is there a way to automatically (or even manually after games are finished) update and move the "participant" columns into the order of total points. So if after game one Mrs_Geek put 42 and Mr_Geek put 3 and you both got it correct, Mrs_Geek would be the first column? Thanks a ton!
Hey one more question,
I actually use the point spread for calculating my winnners, the issue im having is with the National Champ game. Say I think Washington will cover the spread but I think Bama will win the Natty, it wont allow me to do this because of the referenced cells. Any help on this?
Thanks!
Jake
If you don't wish to use confidence points, can a zero be assigned?
@Anonymous RE: not using confidence points...
Just assigned 1 to all games.
I assigned 1 to each person for each game and it is not calculating correctly for some reason. At least I am having trouble finding where it is calculating. I went to the group sheet and people only have one.
Thanks, you rock for this work!!!
I fixed it.
Sorry,
Paul
Love the sheet. I have several players who are older and struggle with the Group Chart trying to see who is ahead. I'm not great with Excel so this may be a dumb question but would it be hard to create a vertical list of players names with the number of correct picks and total points still possible that updates as games progress that could be easily sorted by score?
jal
@Jill, that wouldn't be too hard. I can put it on my growing list of possible enhancements for next year. In the meantime, feel free to take a crack at it yourself if you like. The workbook and worksheets are protected, but not with passwords, so anyone can change anything they like. If you get stuck, give me a holler.
Excel_Geek
not sure what I did wrong, but when I enter the winners into my bowl summary spreadsheet some of the players picks turn green or red if they picked the loser and other players just stay plain black font. Did I do something wrong when I copied and pasted into the spreadsheet?
@Ginny...
I imagine you may have simply copy-pasted instead of using the copy-paste special... values as I always recommend. It shouldn't have an impact on scoring, but the conditional formatting may be hosed. You can always start over with a clean copy of the master file and then re-copy-paste your participants' picks in. Just be sure to use the paste special... values option when you paste.
Any suggestion for how to assign half-values in event of a push? I can modify even scores manually (and assign everyone the same team) but for odd scores, the column will not recognize the .5 as an accepted value. So for instance, in the ODU game, the spread was 4. The final score gap was 4, so the game is a push. In my league, we award 50% of the bet points in the event of a push. So if someone had bet 14, they would lose 7 points but win 7 points. I can adjust that manually (even though the same-score warning highlight comes on). But I cannot adjust an odd score where if someone bet 13, they would lose 6.5 points but win 6.5 points. Any ideas on how to make adjustments? Thanks!
Your sheet rocks!
@Tim RE: pushes...
You'd have to remove or change the data validation from the points columns to allow for non-whole numbers between 1 and 42. I'll add handling of pushes by awarding half of the confidence points to the list of potential enhancements for next year. Thanks for the shout and suggestion.
Later,
Excel_Geek
There's a problem on your summary_chart_data sheet. It looks like you moved the pinstripe bowl to the bottom row on your game_charts sheet and forgot to account for it on the previous one. You also added the New Mexico bowl to the game_charts sheet and its not on the summary_chart_data sheet. You should probably put iferror statements on the bowl rankings part of summary_chart_data, too, in case someone doesn't want to do all 42 bowls.
@Matt RE: problem on summary_chart_data...
Thanks for finding that and pointing it out. I've got it fixed on my end now. Somehow I had just skipped referencing the second game in the list and was a game short. I also found a problem with the CRITICAL GAMES tab -- the teams being referencing in the championship game were the wrong ones -- now fixed. Also, that same issue was being done in the scenario data tab (normally hidden) -- it was picking possible winners of the playoff games from the wrong games. That's now fixed. I'll get the fixed up version uploaded here in a second...
I am trying to copy and paste ten participants into the update and it is not working. I am sure it is user error on my end. It won't let me update winners correctly for future games if I copy and paste. Any advice?
Thank you so much.
@TheBoyfriend...
Be sure to copy paste special... values.
Greetings, Geek!
Are you doing a 2017-2018 NCAA Bowl Games Pool?
Happy to donate again.
Thanks!
Laurie
@Netlaur, yes. I'll have the sheets out soon.
Awesome!
Thanks, Geek!
Post a Comment