Ed's NFL Office Pool Calculator

Each week, from the blue-colored "Player_Picks" worksheet tab, enter the picks of everyone in your office pool. Follow the examples provided in the workbook. If you're playing with a score tie-breaker for the Monday Night game, enter each player's tie-breaker score in the column provided for that. Please note that these picks do NOT have to be in the same order as the games listed on the NFL_Schedule tab! (An enhancement from an earlier version.) You also do not have to enter everyone's picks in the same order as everyone else, although it's usually almost always convenient to do so.

If you're participating in an office pool that uses Confidence Points, enter the Confidence Points below each team. The Player_Picks worksheet tab shows an example of how to enter the data. If your pool uses Confidence Points, in the User_Options worksheet tab, let the program know how you want the program games that end in a tie. (Change the 0 to a 1, 2, or 3.)

Optionally, if your pool uses the NFL pointspread, you must enter each game's pointspread in the NFL_Schedule tab, in the row provided for that. The spread you entered is from the home team's point of view. (For example, If the Detroit Lions are at home and favored by three points, enter -3. If Detroit is at home and is a two point underdog, enter +2.)

That's it! That's all you have to do! You (1) initially enter your office pools picks in the Player_Picks tab, you (2) retrieve them via the Main worksheet tab, and then you (3) set the result for each game, as desired.

As you set the result for each game, the program will automatically do the rest and determine who wins your pool! Whoo hoo!

Prior to the games, during the games, and after the games, the calculator makes it very easy to play "What if?" and see who would win the pool under various scenarios. For example, if the New York Giants had defeated Dallas instead of the other way around, who would have won the pool? With the calculator, it's easy to see. Just change the winner from Dallas to New York. The calculator does all the work for you.

What The Buttons Do

Most of these buttons should be self-explanatory. One or two, however, require an explanation.

This button is best described by using an actual example.

It's early Sunday afternoon and the results of a few games are in. You've entered the winners via that games toggle button, or maybe you've clicked the Download Scores button. You now see your name highlighted in grey. This means the calculator has determined you can no longer win the pool! How can this be? There are a lot of games left to be played!

Use your mouse to activate the cell with your name. (This is known in Excel as making that cell the Active Cell.) Now click this green button. All of the remaining games, that do not yet have have a winner assigned to them, will be set to the team that YOU predicted will win. And once you do this you will see that someone else will have a better score than you do, even if you were to win the rest of your games.

Prior to any games being played you can also select your name and click this button, to see if any players have the same picks as you do.

Clicking this button accesses one of two different websites, to retrieve the final scores.

If you're downloading the scores while the games are being played, a small number will be posted below the Winner row, indicating the current quarter (first, second, third, fourth) the game is in.

When this button is toggled, either the NFL scores of the games are displayed above the team name or the number of office pool participants in your pool who picked that team is displayed above the team name.

Clicking this button saves the winners listed, as indicated by the toggle buttons, to the purple-colored NFL_Schedule worksheet tab. It also populates the Running_Totals worksheet tab.

If the scores of the games are saved, you don't have to download the scores again to view prior weeks results. The program will retrieve them from this tab once it loads everyone's picks for the week you specify.

The Cross Reference Chart

Just for fun, after accessing the Main worksheet tab, switch over to the dark-blue colored worksheet tab labeled Cross_Reference. In a chart form, this tab indicates, for each player in the pool, the number of games they have that are 'different' than every other player! It's a handy tool to immediately see if anyone else has the same exact picks that you do! Wow!

If anyone has just a one game difference with anyone else, the team each player picked will be listed in the chart. That game is a "key-game" for both of those players. (When anyone has a one-game difference with anyone else, that player MUST win that game in order to win the pool.)

A Few Tips

If you wish do to not see the "macro warnings" message each time you open the file, one solution is add the folder where this program is located, to the "Trusted Locations" folder, via the Excel Trust Center screen. (If you access the file frequently, doing this is a nice time-saver.)
When saving the file, you must save it as an Excel Binary Notebook (xlsb) file or as an Excel Macro-Enabled file (xlsm). Note that a binary notebook file supposedly loads faster, saves faster, and should perform calculations a bit faster, than an xlsm file.
When entering player picks in the blue-colored Player_Picks worksheet, you don't have to separate each week by an empty row, as shown in my initial examples, although you may wish to do so for clarity.
After entering each player's picks, take a moment and save the entire worksheet. (Just in case the program crashes later. If it does crash you won't have to re-enter these player picks again. Again, be sure to save the worksheet as an Excel Macro-Enabled Workbook or an Excel Binary Workbook.)
Depending upon how many people are entered in your pool, you may have to resize the rows a little bit, to fit all of them one one screen or to fit them all on a sheet of paper, assuming you're printing them out. (If you're the host of your weekly pool, you can use this program to print out the picks and distribute them to everyone else, prior to the weekend games, after everyone has submitted all of their picks to you.) Note that you can now set the size of the rows in the User_Options tab.
Prior to printing the picks, you may wish to reduce the Scaling feature, to something less than 100%, via Page Setup, to fit everything on one page. (Again, depending upon the number of participants you have that week and the size of your rows and columns.)
You don't need to use the data validation drop down arrow, in cell R1, on the main page. If desired, you can select the week of data you wish to retrieve by simply entering the week number in this cell and hitting your ENTER key.
If you prefer to see the column letter headings, simply turn them back on. (Select VIEW and click the HEADINGS box.) Likewise for the gridlines, which are set to OFF for most tabs.
Are you using a different game, other than the Monday Night Game, as your total points tiebreaker? If so, either update the NFL_Schedule tab and list the game you are using as a tiebreaker last, or simply enter the total points scored in the box provided, and click the NFL OFFICE POOL graphic, to recalculate the winner.
I initially "protected" each worksheet, so that a few of my formulas couldn't be accidentally erased. I've changed my mind about that. Currently, most every worksheet is now "unprotected." (After entering each player's picks in the Player_Picks worksheet, there's nothing else to enter or modify! All you do to interact with the program is to click a few buttons!)
My Visual Basic code is all there, for anyone to see/learn from, etc - I'm not hiding the code at all. (Note: You might learn from this by NOT doing what I'm doing! Again, I am NOT an expert in Visual Basic!)