Sunday, August 23, 2009

NFL Football Weekly Pool Pick Em Spreadsheet Masterpiece - 2011


August 13, 2011 - UPDATED with 2011 Season!!! Check the Spreadsheets Tab.

First, I do realize this spreadsheet may not excite you like it does me, but I'm weird like that. I love my spreadsheets; probably because I love organization, but not to the point that I'll be appearing on A&E's Obsessed any time soon.

And I've created an awesome NFL Pick Em spreadsheet, available free to anyone who wants it. Like many of my spreadsheets, I've been using this one for years, but just took the weekend to completely revamp it. I've saved it as an XLS (Excel 2003) file.

You can get the file (for non-spread pick pools) from my folders on MediaFire here:
NFL Pick Em-2011.xls

And you can get the file (for pools against the spread) from my folders on MediaFire here: NFL Pick Em-2011-Spread.xls

Before I make my picks each week in the office pool, I have a ritual.

  • Find out the favorites.
  • Check out the point spread.
  • Read about the matchups.
  • Check for key player injuries.
  • Consider home field advantage.
  • Make my picks.
  • Predict the Sunday Night and Monday night scores (you need those for tiebreakers).
  • Predict the team who will score the lowest and the highest (also needed for tiebreakers).
  • Choose my survival pool team (pick one team to win each week but can't choose same team twice). I actually have two of those going in the beginning of the season, then I'm quickly down to one.

As the games complete, I update my spreadsheets and see how I fared. I also check out what would have happened if I chose all favorites or all home teams. Each year, I would have won the pool at least once, if I had tried one of these methods, even after the tiebreakers - usually with all favorites. Home team advantage is not as big a factor as local fans would like to think.

So if you're detail-oriented or just want some helpful organization, this is the spreadsheet for you. I embrace my neuroses! Maybe I should be on A&E.

Let me know what you think!


18 comments:

Teddy said...

This is a great spreadsheet. Thanks for making it available on the internet

Mike said...

You're quite welcome, Teddy. I appreciate your note! :-)

~Mike

Daniel said...

Very Nice! How do we get the odds column to work.

Mike said...

Thanks, Daniel! If the away team is favored, I just enter the spread and it shows as positive. If the home team is favored, I include the dash to make it negative number for the spread. For example, this week Atlanta is favored over Carolina by 6 points, so I entered -6. I get my odds from this site every week: http://www.sportingnews.com/nfl/odds.

Good luck!

Josh C Fuller said...

This spreadsheet is awesome!! I did run across a glitch maybe. Some of the picks I made were wrong but they are still showing up as correct, and vis versa. Let me know if you have any thoughts.

Mike said...

Thanks, Josh! The first version had a glitch and I uploaded a second "-Corrected" XLS. It's been working fine for me.

Here's how it's designed: To pick the away team, copy the block with a dot in Column C. To pick the home team, copy the block with a to in Column G. You can actually use any character - I'm just checking to see if Column C or G is blank. For this to work, your choice column needs a character in it, and the other needs to be blank.

In Column A, I compare the scores based on which team you picked (Column C or Column G). If you picked Away (character in Column C), and Column E> Column I, you get a check. Otherwise it stays X.


If you picked home (character in Column G), and Column I > Column Eyou get a checkmark. To pick the home team, copy the block with a dot in Column G. If the score in Column E < score in Column I, you get the checkmark.

Column M gives a "1" for checkmarks. Top left corner gets total results from totals in Columns M & N.

I'm sure there are better ways to do this. I'm a hack so this logic worked for me. :-). You can send me your spreadsheet and I can look at it too. Or you might want to start with a clean (-Corrected) one and see if that works.

Good luck!
Mike

Josh C Fuller said...

So, the function is not taking into account the spread??

Mike said...

Aw snap! It does not take the spread into account. I should have mentioned that. I track the spread for who is favored by how many points. I'm sure I could update the formulas to account for spread. I'll post a new spread-based sheet this week.

Thanks!
Mike

Mike said...

Thanks again, Josh. I play in non-spread pools and hadn't considered the spread pools. I've posted a spread version on Media Fire and added a link to this post above. Let me know if it works for you!

Mike

nfl pools said...

Thanks for posting such a good information. I was looking for that type of information but didn't find.

Anonymous said...

So this is not designed to enter multiple pickers on one sheet?

I have 3 people at work that want in on this and I want to have them all on the same spreadsheet...

Thanks.

Mike said...

Sorry, you're correct and this is not designed for tracking multiple pickers on one sheet. It's really designed for the individual. Sites like Yahoo Fantasy does host Pick Em leagues for free, and it would track everyone. ~Mike

Anonymous said...

I had this last year and was looking for it, was getting ready to look into tapscan to hunt you down, LOL. So relieved i found it for my hubby.

Anonymous said...

Well done - I like the color scheme. I've done a few spreadsheet in my day but never have the patience to make them look as attractive as this one.

Yes I realize I just implied your spreadsheet is attractive ;)

Mike said...

Best comment ever! :-)

Anonymous said...

Hi Mike: Yhis is freekin aswsome!!Do you have this available for the 2012 NFL season? Also, I am in a league where you must assign points to each game (1-16 based on how many games are played that week, of course if there was a bye that week it would be 1-15 or 1-14, etc.? Let me know. Thanks.

Mike said...

Thanks, Anonymous.

Click the Spreadsheets tab at the top of this blog and you'll find 2012 spreadsheets! That's where I post the new sheets each year.

I have not incorporated the assigning points but I've played in a format like that. I'll have to give that feature some thought.

~Mike

Anonymous said...

I’m not that much of a internet reader to be honest but your sites really nice, keep it up!
I'll go ahead and bookmark your site to come back in the future. Many thanks

my homepage - Amino Prime Reviews