Office Football Pool

Chris York comments:

trying to put together a football pool, 100 squares, 10 rows x 10 rows. Cant find the format to fit this idea

Here’s one version I’ve seen.

Here’s how it works:

1. Type the Home and Visitor team names in P1 and P2.
2. Hide the numbers using the option button to the right.
3. Print the worksheet.
4. Go collect 100 initials, one per box.
5. Type the initials in the boxes.
6. Show the numbers using the option button.
7. Print the final worksheet.

The numbers will change every time the worksheet calculates, and that includes when you show/hide the numbers. So be careful that you only show the numbers right before you’re ready to print. The final printed form will have the correct numbers, but the worksheet will not have those numbers if you do anything else that causes a recalc.

Not familiar with this type of pool? Take the sheet you printed in Step 3 around your office and sell each of the 100 squares. When a person buys a square (or squares), he writes his initials in the purchased squares. When all 100 squares are sold, the numbers are inserted in the top row and down the side. At the end of every quarter of the game, you find the winning square based on the last digit of each teams score. If, at the end of the first quarter, Nebraska is beating Iowa State by a score of 19 to 15, the person whose initials are in the top left square would be the winner for that quarter and get 25% of the pot. Do the same for the remaining three quarters.

Of course you should never do this where it is prohibited by law.

Download FootballSquares.zip

Update: Chip added some macros to lock in the numbers and report the winners. You can download FootballSquares20.zip for the macro version. Thanks Chip.

Posted in Uncategorized

35 thoughts on “Office Football Pool

  1. Saw this late so I don’t have time to play with it today. Some comments though.
    Generating the random numbers shouldn’t happen on “calculate”.
    They should be done only once (or twice or a random number of times (let’s not get into that now)).
    Point is that the numbers should be generated only when all the boxes are filled-in and once generated, locked so they can’t be changed again.
    If I could stay up for a few more hours……..
    Cells to enter the 1/4, 1/2, 3/4 & final score.
    Highlight the winning boxes (based on the scores entered above).
    Cells to show the winners and the percentage (amount) won.
    Obviously I’ve never seen one of these before (or gambled).

  2. More…..
    In the pools that I’ve seen, the boxes themselves are usually numbered in the upper left hand corner (starting left to right (then down a row) from 1 to 100). That way the “player” can select “favorite” numbers. This also helps when they call-in (usually to the bar/tavern that is running the pool) so they can ask what their coordinate scores are (based on the “number” of the box that they bought) after the numbers are selected.
    Usually, the prize breakdown is 12.5% for the 1st & 3rd quarter, 25% for the half & 50% for the final.
    Obviously, I’ve never been in a bar (or drank liquor) either.

  3. Didn’t I say that I had to go to bed???
    If you do number the boxes you can use a separate sheet to enter the names, initials (or derive them from the name) contact info etc. and have the boxes populate from that table. Once you have a table of names/initials, everything else will fall into place easily.
    Sorry that I didn’t get to this earlier. I was at the bar having a few drinks waiting to see if I won the Monday night football pool I entered.

  4. John Wilson,

    I actually made almost all the same changes you recommended and sent to Dick via email (having no idea how to make it available online myself.) It makes the numbers fixed on a separate sheet and hides the original one, also providing a place to put the scores and see who the winners are.

    Chip

  5. I’m trying to assign the squares for a 10×10 square football pool randomly among 9 of my friends. Does anyone have a template for this?

    Thanks,
    Jill

  6. Jill: Type your name and your friend’s names in P7:P16. Then put this formula in C5:

    =INDEX($P$7:$P$100,INT((COUNTA($P$7:$P$100)-1+1)*RAND()+1),1)

    Then fill right and down so that it’s in every square of the grid.

  7. Maybe I should have been more specific. I’m sorry! My daughter’s basketball team is doing this as a fundraiser. There are 9 girls on her team. I have to randomly assign the 9 girls squares so they can go out and fill up the 10×10 by selling those squares that are assigned to their names. The amount of squares each girl sells is variable, depending on how many they think they can sell. For instance, one girl signed up for 30 squares and another 10, another 0, etc.
    Your help is greatly appreciated!!! :)

  8. Okay, are you ready? In P7, put

    =RAND()

    and fill down to P106. In Q7:Q106, put the girls’ names. There are 100 cells in that range, and you need to fill them up. If Girl1 committed to 30 squares, then you need to list her name 30 times. If Girl2 committed to five, then list her name five times. In C5, put

    =VLOOKUP(LARGE($P$7:$P$106,((ROW()-5)*10)+COLUMN()-2),$P$7:$Q$106,2,FALSE)

    and fill right and down to fill all the squares. Send me an email if you want the workbook.

  9. Dick, you are a lifesaver! Thank you SO much!!! :) I think I can get it to work, but if not, I’ll be in touch. Thanks again and again!

    Jill

  10. I really like the FootballSquares1.xls. How can I make it so the each square gets a different number (score) assigned for each quarter. The pools I’ve seen have it so that even if the score remains the same for the entire game, each quarter has a different winner. I want to be able to print it up on one sheet without having to make four separate sheets for each quarter. Does that make any sense?

  11. Hi Dick,
    I’m lame because I’m still having problems with this spreadsheet. Sorry! In the 10×10 grid, the first 7 rows calculate beautifully. The last 3 rows produce errors, for some reason. It says there is a problem in Cell H15. I would love to email you the spreadsheet, but I don’t know your email address. Thanks again for all your help!

    Jill

  12. Dick,
    Thanks,I think I figured it out. I changed the formula to be
    =VLOOKUP(LARGE($P$7:$P$106,((ROW()-7)*10)+COLUMN()-2),$P$7:$Q$106,2,FALSE)
    and that seemed to fix the last 3 rows.
    You have been VERY helpful, as there is no way I would’ve been able to come close to figuring out this formula on my own.

    Thanks again,
    Jill

  13. Dick,
    Thank you for the update. I’ll try to figure out if I can use this. I guess I’m just more familiar with one name per square and four different numbers for each row/column. I think the problem I’m going to face is getting the names to fit in each square. I can’t use initials due to my situation for this pool. It isn’t a “money” pool. (I could explain but it would take some time.) I also need to post this sheet and I want it to be on one piece of paper and legible (some of these guys can see the future with their spectacles…lol) If you can still help, I would appreciate it. If not, then thank you so much for the work you put in already. You have still saved me some effort. Thanks again!

  14. Still working on this but it’s late in the season and after Sunday I probably won’t revisit it for a while. Lots of extraneous code (and probably a few glitches) but it works (and is being used by the bar I frequent (and I’m in the pool too)). Note: Double-Click on any box to enter names and initials. Will try to clean this up someday but it does work.
    http://nbpl.home.att.net/Downloads/sb2008.zip

  15. […] John Wilson wrote a fantastic post today on “Comment on Office Football Pool by John Wilson”Here’s ONLY a quick extractStill working on this but it’s late in the season and after Sunday I probably won’t revisit it for a while. Lots of extraneous code (and probably a few glitches) but it works (and is being used by the bar I frequent (and I’m in the pool … […]

  16. After that last comment (and thank you kindly), I realized that while that workbook I posted looked pretty and did a lot of neat things, it still had a number of incomplete routines on it. I’ve since updated the file (same link as my previous post) and cleaned up (and completed) most everything that needed to be done.
    Currently it’s set up for this years SuperBowl although it may be changed to work with any football game.
    In redisigning it, I tried to make provisions for a much more useful version (hopefully before next season). Specifically what I want to do with this is make a single workbook that will work for an entire season. Using additional sheets (and the appropriate coding) it should be possible to create multiple pools (and save them) for an entire season of games.
    Suggestions, comments, criticisms, etc. welcomed.
    http://nbpl.home.att.net/Downloads/sb2008.zip

  17. do you have to have 100 squares for a card? I know it sounds like an utterly stupid and mathmatically impossible question but I seem to remember being involved in a pool a few years ago that did not have 100 squares. The reason I ask is that we will not have enough people to fill the 100 squares.

    thanks!

  18. Nikki,
    That particular type of pool has to have the 100 boxes. It’s based on the rightmost digit of the scores for each of the quarters and there are exactly 100 possible combinations. People usually do take multiple squares in these pools depending on the “points” per box. If you have ten people, each can take ten boxes. There are many other types of pools (when it comes to gambling, people devise many different ways to throw away their money (although the winners usually think differently)). If you search Google for “Football Pool” you should be able to find a number of them (but most will be based on the 100 box scenario).

  19. […] Create a Football Betting Squares Sheet. Football betting squares are commonly used for the Super Bowl, but can also easily be used on a week to week basis for any one football contest. If you’re familiar with the concept, you can make your own with pen and paper. Otherwise, we suggest you download an Office Pool Squares Document – Click here for an Excel Version. For specific instructions regarding how this particular Excel football squares document works, Click Here for Excel Document Football Office Pool Directions […]

  20. Name is Felipe Lamas… I’m wondering how I can control a Football pool to calculate the W-L per week and have a standing bracket for the year? here an example …can you make this better and easier? thanks again.

    Elkhunter1CHI CIN IND NYG NE TB TEN HOU NO MIA SF DAL SD MIN 21
    Gimpy CHI CINiND NYG DAL TB JACHOU NO BUF SF DEN PIT GB 41
    MadMaxxCHI CIN IND NYG NEWAS TEN HOU NO BUF SF DAL PIT GB 35

    Immortal Tech(37-11) .770
    Jacob Perea $(36-12) .750
    Lennox (36-12) .750

  21. This is a must in Packerland. I goofed around trying to write the logic for =randbetween(0,9) and make it work so as to show a discreet number in each box of the row/col. Gave up and tried to search. This is the best but I don’t understand how it works. Is there some macro or something in this sheet?

  22. Greg: Unhide rows 18 through 28 and look in columns D:G. I put 10 random numbers in the first column and the digits 0-9 in the second column. Then I use the LARGE function to find the largest to the smallest rand numbers and the corresponding digits.

  23. Been looking for a Football pool, 25 square since ill cant fill the 100 square pool, modified your pool, not a expert on excel, just need to print out the football pool sheet.

  24. If you need something less that 100 squares, just make the squares cheaper and sell more of them. So for 25 squares, you price them at ‘buy one, get three free’. That way you still have every number covered, but you only need to sell 25 squares.

Leave a Reply

Your email address will not be published. Required fields are marked *