Using Excel to Calculate Your MegaMillions Winnings

Sign in to queue


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)

Generic Episode Image

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!



The Discussion

Add Your 2 Cents