Using Excel to Calculate Your MegaMillions Winnings

Download the spreadsheet here:

There's currently a lot of attention on the MegaMillions lottery here in the United States.  The MegaMillions lottery is a multi-state game of chance which has a cumulative jackpot that increases every time a drawing is held and there is no winner.  Why is there a sudden interest?  Well the jackpot has passed the previous record and is now worth over $640 million.

If you're going to play or would like to play along without risking any money, we put together a quick spreadsheet that calculates your winnings.  It has three parts that need input: your numbers, the winning numbers, and the jackpot amount.  You can check up to 100 tickets on this template, but you can always add more rows, the equations should update as well.

Your Numbers:

Input Grid

Winning Numbers (and jackpot amount)

The calcluations are done in a few steps:

First, the number of normal number matches are counted using an array function: {=SUM(COUNTIF(A2:E2,$T$2:$X$2))}

The MegaBall number is a simple countif: =COUNTIF(F2,$Y$2)

There are nine ways to win in the MegaMillions, so there are actually nine hidden columns that calculate individual wins per ticket. The Winnings column, is a simple sum: =SUM(I2:Q2)

Hidden Calculations

Enjoy and good luck!


Follow the discussion

  • Oops, something didn't work.

    Getting subscription
    Subscribe to this conversation

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.