# Using Excel to Calculate Your MegaMillions Winnings

• Posted: Mar 30, 2012 at 2:15PM
• Average: 5

(3)
• 9,383 views

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.

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)

Enjoy and good luck!