MegaMillions Lottery Pool Calculator in Microsoft Excel


lottery-pool

Summary:

Have you ever held a lottery pool at your work or office, but find it difficult to manage who paid, how many tickets to buy and how much everyone won?

This Microsoft Excel document will help you manage all of your MegaMillions Lottery tickets for your lottery pool.

Unfortunately this Lottery Pool Calculator will only work with the MegaMillions Lottery, which is featured in the follow states: California, Georgia, Illinois, Maryland, Massachusetts, Michigan, New Jersey, New York, Ohio, Texas, Virginia, and Washington.

For more details on the MegaMillions, click here.

What is a Lottery Pool?

A Lottery Pool is a group of people that all contribute a relatively small amount of money (i.e – $5 each) to a pool to purchase lottery tickets.  The Lottery Pool moderator purchases “easy pick” tickets with all of the money collected.  All of the earnings from the winning tickets is then evenly distributed to all of the members of the lottery pool.

Example:

  • 20 people contribute $5 each for a total of $100 or 100 lottery tickets.
  • Of the tickets purchased, 5 are worth $10, 2 are worth $1000, and 1 is worth $15,000.
  • That’s a grand total of: $16,050.
  • Split that evenly between the 20 people and each member of the lottery pool gets $802.50.

How to use this Lottery Pool Calculator:

There is 3 sheets in the Excel document; MegaMillions Calculator, Lottery Ticket Numbers, and Group Members.

lottery-pool-sheets

Start on the MegaMillions Calculator sheet. In the yellow cell, type in the contributing price of each member.

lottery-pool-price

Next click on the Group Members sheet.  Type in the name and contact information for each participating member.  As you collect the money from each member, mark them as Paid, by selecting Yes from the drop-down box.

lottery-pool-members

After you’ve collected all of the money from the group members, click on the Lottery Ticket Numbers sheet.  It will tell you how many tickets you need to buy.

Purchase the lottery tickets with the collected money and on the Lottery Ticket Numbers sheet type in the numbers for each Lottery ticket (yes, it can be time consuming if you bought a lot of tickets!).  You’ll notice that the Lottery Pool Calculator will keep track of how many tickets you have left to enter.

lottery-pool-numbers

Once you’ve completed adding all of your purchased lottery ticket numbers, wait for the big night, the winning numbers. When the winning numbers are announced, click on the MegaMillions Calculator sheet and type in the winning numbers in the green cells.  Also type in the Jackpot (in millions) in the blue cell.

lottery-pool-winning

Now here’s the fun part…

Select “Yes” for Activate Results (in the red cell).  This will output your winning results and how much each member of the pool should receive.

lottery-pool-results

To see the winning tickets, click on the Lottery Ticket Numbers sheet and you’ll notice the highlighted rows for the winning tickets.  The color of the highlight corresponds to the winning value found on the MegaMillions Calculator sheet.

lottery-pool-detailed

That’s it!  Collect your earnings, distribute them equally between the  group members, rinse and repeat!

08-09-2010 UPDATE! I’ve removed the password on the lottery pool calculator, so you can now edit it!

If you’ve revised the Lottery Pool Calculator for other lotteries and would like to share, please email me and I would love to share your version with the community!

Download MegaMillions Lottery Pool Calculator:

Disclaimer:

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

http://www.gnu.org/licenses/gpl-3.0.txt

Be Sociable, Share!

, , , , , , , , , , , , , , , , , , , , , ,

  1. #1 by Phil Clark on January 31, 2010 - 10:25 am

    South Carolina started playing the MegaMill Lottery today, 31 Jan 2010. I’ve just discovered your wonderful excel file for the MegaMill Lottery. Your Disclaimer states “This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License…” When I tried to “UnProtect” the worksheet(s) it asked for a password. Will you please share the password so I can edit the worksheets for my personal use? Thanks so much for your cooperation.

    • #2 by Anthony on February 5, 2010 - 12:04 am

      I’ll release an update to the Excel doc free of the password, so that it can be modified soon! Thanks for your interest!

    • #3 by Anthony on August 9, 2010 - 10:32 pm

      I’ve recently re-released it without the password protection so you can modify it. If you are revising it for a different lottery, I would love to feature your revision for other lotteries. Enjoy!

  2. #4 by greg chavez on February 14, 2010 - 12:08 am

    hey i love your mega millions spreadsheet, it works great
    a few weeks ago powerball came out in NJ and i wanted to use the same excel sheet because i like the layout of yours. so i unlocked the excel file and made a quick powerball edit by changing the data validation field

    http://www.2shared.com/file/11369544/2a31822d/powerball.html

    would you be able to combine them into one? and i kept tinkering with trying to make a powerplay slot but it looks like crap and i dont know any formulas.
    keep up the awsome work

    • #5 by Anthony on February 19, 2010 - 12:15 am

      Way to go dude! Awesome to see a variation on my original! I’m going to leave the link right now in your comment if other would like to download it, but I’ll take a look at how Powerball is done and I can create a version that would allow such things as the Powerplay slot. At this point I would more than gladly provide a second link to the Powerball version of the spreadsheet!

      I appreciate the added work and consideration, thank you!

  3. #6 by Sarah on February 24, 2010 - 11:09 am

    Just tried this spreadsheet out myself, it works great!

  4. #7 by alex broom on July 15, 2010 - 4:07 pm

    hey the mega millions spreadsheet its a shame it only works for this though and not for other lotteries

    • #8 by Anthony on August 9, 2010 - 10:31 pm

      I’ve recently re-released it without the password protection so you can modify it. If you are revising it for a different lottery, I would love to feature your revision for other lotteries.

      • #9 by Phil on November 26, 2010 - 5:34 pm

        I live in Florida, can you revise for Powerball, and lotto?

        Thanks
        Phil

  5. #10 by Randy on July 26, 2010 - 1:34 pm

    Hey there, I was curious if you’ve had a chance to either protect with a blank password, or publish the password to unprotect the document? It looks great, but I’d like to modify it for LottoMax up here in Canada.

    • #11 by Anthony on August 9, 2010 - 10:30 pm

      I’ve recently re-released it without the password protection so you can modify it. If you are revising it for LottoMax, I would love to feature your revision here. Enjoy the unprotected doc!

  6. #12 by Char on July 28, 2010 - 10:23 am

    Hello Anthony,

    I’ve tried your spreadsheet and I think that it’s great. I would like to make some modifications however, so will you please send me the code to unlock it.

    Thank you in advance for sharing this great product.

    • #13 by Anthony on August 9, 2010 - 10:29 pm

      I’m glad you like the doc, I’ve recently re-released it without the password protection so you can modify it. If you are revising it for a different lottery, I would love to feature your revision for other lotteries.

  7. #14 by Junior on September 10, 2010 - 12:06 pm

    I’m looking for something like this but for the NY Lotto.
    Does anyone have one they can share with me. I tried editing the mega millions one but I had no luck.
    Thanks
    Junior

  8. #15 by Phil on November 26, 2010 - 5:52 pm

    Hello Love this, and thank you. Can you do a Powerball and Florida lotto as well.

    Thank you
    Phil

  9. #16 by Excel Fan on December 30, 2010 - 8:02 am

    I am very interested in doing sophisticated calculations in excel.
    It is an amazing tool. The file is used Microsoft excel very good to achieve its goal.
    I am sure using such excel files can help our lifes alot.

  10. #17 by michael lewis on January 1, 2011 - 7:17 pm

    do you have one for the powerball

    • #18 by Anthony on April 8, 2011 - 12:14 am

      I do not, but the one I have is open to modify to your liking.

  11. #19 by Dena on February 24, 2011 - 2:47 pm

    The link in Greg Chavez’s email from 2/14/10 does not work. Can someone please publish the Powerball version of this spreadsheet? It makes a great app. Thanks.

  12. #20 by Dena on February 24, 2011 - 3:05 pm

    Update: I managed to update the validation to make it work with PowerBall — what a great spreadsheet. Now I have a new challenge.

    Can this be made to work when you parlay past weeks’ winnings into the kitty? for example, my group won $10 this drawing, which we will add to our $2 each for the next drawing. How would I modify the spreadsheet to allow for extra tickets without extra participants? Thank you!

  13. #21 by Dena on February 25, 2011 - 5:29 pm

    2nd Update: I managed to modify the new PowerBall file to include an entry for “parlay,” so winnings from previous weeks can be included to increase the total number of tickets while maintaining the correct number of players. I will forward to anyone who requests (I have no place to post files for download).

    • #22 by amy on March 10, 2013 - 1:16 pm

      I would LOVE a copy of your spreadsheet! I have been looking everywhere for one to do PowerBall or Lotto!

  14. #23 by Dan on March 21, 2013 - 5:19 am

    Dena, can you email me the powerball file you made? Thank you! Dan danrgray at gmail com.

(will not be published)

Security Code: