Ed's
NFL Office Pool
Spreadsheet / Calculator


Version 3.0


Now available for
the 6th straight year!

Updated
with the 2017
NFL Schedule!

Updated
to support the
Chargers move
to Los Angeles





Program Revisions

 

Version 3.0


Removed all of the Active-X buttons and replaced them with a customized graphic. (I got tired of people writing to me, telling me their Active-X Buttons had suddenly become "messed up." It was always a graphics card conflict / issue and NOT a bug in my program.)

Recoded the program to allow for four additional weeks, to accommodate the playoffs. (The Running Totals worksheet tab was updated, as was the NFL_Schedule tab, and the Weekly Pick Sheet Form.)

Replaced all references of San Diego Chargers to Los Angeles Chargers. And now with two teams in Los Angeles, LAR is now the abbreviation I'm using for the Los Angeles Rams (and not just LA), and LAC is the abbreviation I'm using for the Los Angeles Chargers.

The program should handle the upcoming Raiders move to Las Vegas with no additional changes. (The two-letter city abbreviation used by the NFL will most likely be LV.)

Entered the 2017 NFL Schedule.

Removed a few of the speed bottlenecks, so the program runs a bit faster. Yay!

The macro in the hidden "data" worksheet now also updates the NFL_Schedule tab, when it downloads the NFL schedule for the year.

Despite the speed improvements, very large pools (100 or more players) still take several seconds to load, as the programming logic goes through the sub routines to to determine the winners, to highlight the necessary cells, to determine who is still alive and who has been mathematically eliminated, etc. Thus, I thought it best to add an optional "progress bar" in the Status Bar area (bottom of the screen), so the user, if using a slow computer, knows that "something is happening" - that the program is still working and hasn't crashed.

Allowed the user to set the number of maximum players themselves. (It was initially hard coded to 200 and then later hard coded to 500.) In theory, with a smaller number, the program should run a bit faster. If you only have a dozen or two members in your pool, just set the max number of players to 30 or so.

Removed the code that generated the Checksum figure. (This was an old feature I needed a long time ago, that I'm sure is not used by anyone.)

Added the Testimonials worksheet tab. (Feel free to hide it so it is out of the way or delete that tab entirely.)

Added an option for the user to download the game scores from either the NFL website or the CBS sports website.

Added a CLEAR DATA button in the Running_Totals worksheet tab.

Cleaned up the weekly pick sheet forms, so the formula doesn't display that funny "0" during the weeks with byes.

Dozens of minor cosmetic changes were made to the program, too numerous to mention.





Version 2.31

The program was attempting to add the spread to the score of each NFL game.  When the picks are first loaded, there IS no score.  The games haven't been played yet and the score is displayed as a dash/hyphen.  You can't add a numeric field (pointspread) to an alphanumeric field (a dash).  An error results.  Fixed.




Version 2.30

Changed the buttons in the Running_Totals worksheet tab from an Active-X button to a Form Control button.

Under rare conditions, players who were still mathematically alive were being displayed as if they were not. Fixed.

Entered the 2016 NFL schedule.

Replaced all references to St. Louis Rams with Los Angeles Rams.

Replaced Jac with Jax in the code, where necessary.  (The NFL now seems to be using JAX as the three-letter abbreviation for Jacksonville.)

Added my Weekly Pick Sheet Form to this workbook.




Version 2.29


The changes made to 2.28 caused a potential bug when loading games. Fixed.




Version 2.28


The program was not stripping leading or trailing spaces from the player's Monday Night point total prediction. There shouldn't be any trailing or leading spaces, of course… it should just be a number, and the program IS computing the difference correctly, from the actual Monday Night total.  Still, if a prediction is entered with lots of such spaces, the number isn't centered in the cell and it looks funny. Fixed.

Apparently the player name and player picks can contain ascii character 160, in addition to leading or trailing spaces. (Via picks sent to the host via a cell phone??) I was only checking for and removing the spaces, not this weird "invisible" ascii character. And, if ascii character 160 was present, the program didn't recognize the team name.  Fixed.

Increased the limit to the number of pool players form 200 to 500.

Fixed a glitch. Under certain circumstances, the program wasn't properly displaying who was still alive and who was eliminated, despite this option being set to "Yes" in the User_Options tab.  (It just required adding a dash ("-") in the NFL_Schedule tab for the score, for all unplayed games.)

If a user entered a Monday Night score prediction as a non-integer, the program was rounding it up or down accordingly, to the nearest integer. This meant a total score prediction of 55.1, for example, would get rounded down to 55. If another player entered 55 as their prediction, the program considered these two players to have same Monday Night tie-breaker score, and graded accordingly. This isn't the case, of course. A prediction of 55 is closer to a final score of 47, for example, than 55.1 is to 47.  Although I see NO advantage in guessing scores as non-integers (only disadvantages - think about it), if a player wishes guess this way, the program should grade accordingly.




Version 2.27

Are you kidding me? Updates somewhere created a bug, with bye weeks, when no bug existed before. The program expected another game was still to be played, and didn't calculate the final winner. This is fixed... I think.




Version 2.26

The Player Nickname sorting link, the Total Wins sorting link, and the Win % sorting link, all located in the Running Totals worksheet tab, didn't seem to be working for everyone. I removed the links and added a toggle sort button instead.

Corrected a mistake in the Schedule, in the NFL_Schedule tab. (Washington hosts Philly in Week 4, not Tampa Bay.)

Added a message in the Running_Totals tab, to let the user know this tab is populated by hitting the SAVE SCORES button on the Main worksheet tab.




Version 2.25


The Player Nickname link, in the Running Totals worksheet tab, that sorts the list alphabetically, wasn't working. (Seriously?) Fixed.

Tiny glitch with the colors of the toggle buttons not working, involving St. Louis. Fixed.

Although no one has reported any bug when the Price Is Right Scoring option is set to Yes, I think I fixed a potential bug.

Pools with 100+ players run very slowly, after the player returns to the Main worksheet tab after visiting the User Options tab, even if no options were changed. Now, if no options were changed there should no be no delay.

If the user modifies the Monday Night Total score in cell S10 (because their pool uses a different number. For example, just the score of the winning team), the program will now SAVE the score the user enters in cell S10, in the Running Totals tab, when the scores are saved.

If the user did enter their own Monday night point total in Cell S10, the highlight colors depicting which players were still alive and which players were dead was not quite correct. Fixed.




Version 2.24


Entered the 2015 NFL Schedule.




Version 2.23

I eliminated one of the worksheet tabs. I combined the NFL_Schedule tab and the Saved_Results tab into one tab. (They basically contained the same data anyway.) I also redesigned that tab, to be more consistent with nfl.com's website, in regards to the way the final scores are displayed.

When the scores are saved in the NFL_Schedule tab, I'm setting the font to bold for the winning team and the winning score. (Again, consistent with the nfl.com's website, and what you often see on television.)

When a team didn't score any points in the game, the Saved_Results tab (now the NFL_Schedule tab) was displaying their score with a dash, instead of a zero. Fixed.

I removed the toggle button that changes all winning teams to the Home Team or to the Visiting Team. (That button was nothing but useless fluff.)

When a prior week was loaded that contained a tie game, the toggle button didn't display the word TIE. Fixed.

Rewrote the code that shades the cells of all non winners, after all games have been completed.

If the program was opened and the Display NFL Scores checkbox was not checked, and then immediately checked, the program crashed. Fixed.

User Request. Added a new user option to allow the same player to submit more than one set of picks and yet combine all of these results into the same player in the Running Totals worksheet tab. The player's picks should be appended at the end with parenthesis. Example: Edward (2), Edward (3), etc.

Renamed the SAVE DESIGNATED WINNERS button to SAVE SCORES & RESULTS. Changed the size of all four buttons (and the checkbox) so they are shorter (but wider) to allow for more data vertically.

For Week 1 of 2014 (for example), the program downloads the scores from www.nfl.com/scores/2014/REG1. Rather than hard-code the year (2014) into the program, it's now a variable that the user can change themselves each year, in the User_Options tab. Now the only other thing that needs to be changed each year is the NFL schedule, and the program will work each year, with no other modifications.

Added an option for the user to set their preferred row height for the Main tab and the Running_Totals tab.

Added the user option to auto scroll to the top rows of most worksheet tabs. (All but the Player_Picks tab.)

Added a hyperlink to the name of this program at the top of this page, to the calculator's website.

When saving the final results to the Running_Totals tab, the total wins for the player who won is now set in a bold font.

Bug found: The program didn't recognize "Buccaneers" or "Chiefs" as valid mascot names. Fixed.

Removed the instructions on how to get rid of the "Data Connections Are Disabled" message, since I think I've solved that for good.

If some of the teams in the NFL_Schedule tab were listed in uppercase, they didn't match the player's selections if entered in lowercase. Fixed.

Dozens of minor cosmetic changes made to the program, too numerous to mention.

 


Version 2.19


Replaced the TOGGLE SORT button in the new Running_Totals worksheet tab with Header Links instead. Single clicking the Header Link will sort that column!

"SL" is now a valid entry for the Cardinials, "NOS" is now valid for the Saints, "NEP" is now valid for the Patriots and "sd" and "Sd" are now valid for the Chargers. (Thanks Joe.)

If an office pool's rules didn't require the user to select all of the games, the games and buttons weren't always loading correctly. Fixed. (Thanks Tony.)




Version 2.18


The logic to determine who was still alive got screwed up.  Fixed.




Version 2.17


The program now recognizes even more teams abbreviations, as well as a few common misspellings. For example, it knows that "Cheifs" = Kansas City Chiefs, "Id" = Indianapolis Colts, "Boys" = Dallas Cowboys, "Gaints" = New York Giants, etc.

The program now can handle player picks that are entered in different columns than one another!  To clarify, when entering picks in the Player_Picks tab, the user doesn't have to keep the same teams together in each column!  All that matters is that each player submit a home or a visiting team for each game.  The user can enter them in or out of order if he/she wishes.  The program will sort everything accordingly!  (However, for clarity, the user should enter each game in the same column.)

I improved the screen display for office pools that don't use a total score tie-breaker for the Monday Night game.

I removed the requirement for the user to designate if their pool uses Confidence Point scoring or not. The program can again determine this on its own.

I added the Running_Totals worksheet tab, and all the logic behind it, at the request of several users.  To populate it, you click the Save Designated Winners button.

The program now downloads past weeks scores, if requested, from the nfl.com website, rather than the repole.com website I was using. (Reason: The repole.com website is down and it doesn't appear it will be up again any time soon. So let's retrieve past scores from another website, and I might as well use nfl.com.)

After the final game had been played, the program didn't highlight the player who won the pool. Instead, the program was only highlighting the top x number of players who all were tied at the top with the same number of wins.  It didn't go one step further and determine the overall winner, by taking the tie-breaker score into effect.  It does now.

I removed one of the buttons. (Slightly less clutter on the screen this way.) The SET all VISITING TEAMS as WINNERS button and the SET all HOME TEAMS as WINNERS buttons have been combined. This button is now a toggle button.

Added a Status Bar message after most actions.

If the user entered their own scores in the Saved_Results tab (which a user wanted to do, because they were no scores available yet to download from the repole.com website, it turns out the user had to enter them in the same order as in the Player_Picks tab!  Darn!  Not ideal.  If they entered them in the same order of the NFL_Schedule tab, the program didn't work.  This is fixed. The user can now enter the winners and final scores in the Saved_Results tab in any order. (But the user should still let the program enter these scores, if possible.)





Version 2.16


Gosh darn it.  I found two mistakes in the NFL schedule.  (Not my fault, other than I didn't make time to double-check it.  The source I got the schedule from was wrong!)





Version 2.15


The calculator will now, optionally, take into account the NFL pointspread when determining the winning team. (User request - their pool uses the pointspread.) The user must enter the pointspread themselves each week, in the NFL_Schedule worksheet tab.

Sorting the list of players automatically, by the number of wins, is now a user option.





Version 2.14


If the user entered a player's name in the Player_Picks worksheet tab, but hadn't yet filled in that player's picks (because they didn't have them yet, but were expecting them) the program crashed.  Fixed.  (Thanks Keith, for letting me know about this.)

If you're using Confidence Points, the program will now check to make sure that everyone's combined Confidence Points equal the same total.  If not, the user will be alerted with an error message.

I cleaned up and changed the format of the Main tab! I think it looks a lot better!  As you can see, the user buttons are now at the top of the screen, rather than at the side.  Also, each player's Monday Night tie-breaker score is now displayed, rather than keeping it hidden. Also, the all-important difference between the player's Monday Night prediction and the actual total is displayed.

I spent more time testing the program when Confidence Points are used, and I may have fixed a few potential bugs (all of them just cosmetic in nature).

When the picks are retrieved via the Main Page, the Print Area is now optionally set, and is based upon the number of players who are playing that week, to make the page more attractive when printing.

The very first time the workbook is opened (and not saved) a brief message appears informing the user that picks must be entered in the Player_Picks worksheet tab. (This is the #1 question that I get e-mailed about. Despite all of my instructions to the contrary, new users begin entering on the Main worksheet tab instead of on the Player_Picks tab.)

Fixed a small bug that would have occurred if the user clicked the Download NFL Winners button, while it was halftime during one of the games.

If a user entered a player's picks that contained one or more leading or trailing spaces, the toggle buttons didn't recognize this pick (with the "invisible" space at the beginning or end) and wouldn't work correctly. This is now fixed.

Added the User_Options worksheet tab, and all the logic behind it.

Increased the size of some of the variables that would have resulted in errors with pools of more than 100 players.

The program now retrieves past game scores from http://www.repole.com/sun4cast/stats/nfl2014lines.csv.

Allowed the user to enter the picks in any format (city name, mascot name, etc.) they wish. The program will make the necessary corrections.


Many other updates too numerous to mention.





Version 2.13

Entered the entire 2014 schedule.






Version 2.12

Added a few more checks to convert invalid team names entered by the user into the proper two or three-letter abbreviation.





Version 2.11

Another minor bug fix, related to the 2.06 bug fix. (When the spreadsheet was saved, closed, and then re-loaded, the week wasn't always being updated correctly.)





Version 2.10


Corrected another typo found in the 2013 schedule.

Added a secret checksum routine, so that when a co-worker and I each enter everyone's picks each week, we can instantly see if our entries match, indicating we each made no mistakes, by now comparing the checksum. If the checksums don't match, we know at least one of us has made a mistake. (After loading the games, ATL-C displays the checksum.)





Version 2.09

Now that I've learned a little bit more about Excel and VBA, I've cleaned up the code a bit. For example, the user can now rename the six worksheet tabs, if desired, and the program won't crash. (The names of the tabs were hard-coded prior to this.) A few other internal enhancements / changes were made.

Found a typo (bug) when retrieving the data from prior weeks. The saved scores weren't always being pulled in correctly. Fixed.

If your office pool uses Confidence Points, the program began calculating which players are still alive when there were 11 games (or less) remaining. However, with slower computers, 11 games is still too many games... the program takes too long. (Excel is slow......) This default is now 8 games and this number can now be set by the user. (You do this above, by modifying the last two characters in the DETERMINE WHO IS STILL ALIVE paragraph, on the Help tab.

The program now automatically switches to full view mode upon startup. (Hit ESC to get out of this mode.)

Very minor bugs found, when I spent some time and attempted to make the program crash. Fixed. For this reason alone users should download and use this version.





Version 2.08

Last year our office pool didn't use the Monday Night Game as a tie-breaker. We're doing so this year and I can already see a few potential problems. For one, the program was sorting based upon this tie-breaker... before the Monday night game has been played!  It's sorting on a 0-0 score!  This is confusing and makes no sense.  Fixed.  Now if your office pool is using the Monday Night game as a tie-breaker, and if this game has a score available, the program will then sort based upon player score, tie-breaker, name.  Otherwise it just sorts based upon player score and then name.

Added this Version History section to the program. (It currently just existed on the website.)





Version 2.07

I didn't fully fix the previous bug.  (And I'm not convinced it's fully fixed now.  More testing is called for.)





Version 2.06

If you saved the worksheet, and then re-opened it, the program didn't know how many players there were, and didn't update the scores correctly.





Version 2.05

Modified the program to now allow the user to enter Confidence Points.  (Good idea, Scott.  Thanks.)

If the user selected a week that had no picks yet, when then selecting another valid week, the font and formatting changed undesirably.

Removed two buttons and three checkboxes.  (The Main worksheet tab was becoming too cluttered!)

Reformatted the Player_Picks worksheet tab a little bit.

Cleaned up some of the worksheet tabs.  (Hid columns, removed Excel headings, etc.) 

Changed the initial week from 1 to 17.  (Forcing the user to change it back to 1 to pull in the data.)





Version 1.04b

Corrected a typo in the NFL schedule.





Version 1.04a

Updated with the entire 2013 schedule.





Version 1.03

Whoo hoo!  I just found out my calculator is being used by someone else! 

And they told me they had a problem, initially.  They entered the teams in uppercase, something my program didn't expect.  (Despite the fact that's exactly what my help file said to do.  Whoops.)   Although they figured it out on their own, I needed to fix this. 

With Version 1.03, the user can now enter each player's picks in uppercase or lowercase. The program will convert everything to the proper case that is required when it comes time to load them from the Main tab.





Version 1.02

Modified the program to accept a TIE game as a possible final result. (This past weekend, the Ram/49er game ended in a tie.)

The program was already capable of correctly determined your office pool winner if a tie occurred. The user would simply not enter a winner for that game - they would leave that toggle button alone, at its initial "neutral" state.

The problem with that, however, was the program logic considered that game was still had yet to be played... and thus the logic to determine which players were still alive and which players were mathematically eliminated wasn't giving correct results.

I'm a perfectionist... this wasn't acceptable to me, even though the chances of another tie happening in the next several years is remote.

This is now fixed.

To set a game as a tie, simply enter a "t" in the cell above the toggle button (row 2) and then click on the toggle button. With this version, the programming logic first looks to see if a "t" is entered in that cell. If so, the "t" that was entered will be erased and the program will mark that game as final, with a tied result.  Also, if you choose to set the toggle button results automatically, by downloading the NFL results, the words TIE will now appear in the toggle box, for all games that end in a tie.





Version 1.01

Fixed a slight display glitch involving the Winner Toggle Buttons.  (At times, "n/a" was being displayed, and the button wouldn't toggle, when it should have been able to.)





Version 1.0

First release to the general public.

 




Version .9b 

Added code to help check to make sure the names of the teams are being input properly, in the Player_Picks worksheet tab.

Fixed a bug - the small "H" wasn't being displayed (to indicate Halftime) above the toggle buttons, when downloading the scores during halftime of a game.

Fixed a bug in the Cross_Reference chart tab, when displaying the help message, which would have occurred during a week when no one had a one-game difference with anyone else.





Version .9a

Added the cross reference chart tab.

Rewrote portions of the code, now that I'm becoming more familiar with the syntax of Visual Basic.

Allowed the user to enter the weekly picks in ANY column order... meaning, the games no longer have to sync up with the games as they are entered on the NFL_Schedule tab.





Version .8a

Replaced the old NFL graphic with the graphic currently used by the NFL.

Added the code to save winners to a new tab, so the scores wouldn't have to be downloaded again, each time that week was viewed.

Added the logic to accept and handle the total score in the Monday night game, as a tie-breaker.

Turned off the display of headings and gridlines on most tabs.

Rewrote the Help tab.

Repositioned most of the buttons.

Repositioned the title, week number, and number of weekly players in the otherwise unused section of the upper-right corner.

Reduced some of the screen flicker when a few of the buttons were accessed.

Changed the week selector into a data validation drop-down box.

Cleaned up the rows - when a week is accessed that had fewer entries than the previous week that was just viewed, empty rows are now deleted.

Changed the spreadsheet format - instead of 17 different tabs, there are now just TWO tabs... one to hold all of the player picks and the Main page which retrieves them.

Changed the buttons from Excel "Toggle" Buttons to Excel "Command" Buttons.

Eliminated the need for the data in the hidden cells, in columns W through AK.

Color coded the buttons to match the home/visitor team, when clicked.

Coded the programming logic to retrieve past scores, for prior weeks.

Added the logic to download the score of the games from the NFL website.

 

Home   /   Necessity   /   How To Use   /   FAQs   /   Comments   /   Donations