It seems nowadays everyone has an office pool for the Power Ball, pooling cash into a large pot and buying enough tickets to cover the cost of them so you all share in the massive jackpot. But the pain with having a lot of tickets is the fact that there are winners other than the jackpot, smaller wins that can be buried in 10, 20, or even 100 tickets bought for a pool. So how do you properly account for tickets and check them against the smaller prizes beyond just the jackpot?

With the daunting task of having to track 33 tickets tonight against the jackpot, ensuring I didn’t throw away any smaller winnings, I did what all good nerds do…I fired up Excel! By utilizing the Conditional Formatting system inside of Excel 2010, I setup rules to compare the values of all the winning pulls and compared them against each individual result to mark it green if a winner and red if a loser. Then I did the same for the individual Power Ball number, again green highlight for winner and red for loser. Using the COUNTIF function, you can create a logical statement to add the formatting

But that still leaves me to check each of the 33 lines individually for a winner, something that would have kept me up half the night. So again using the COUNTIF statement and some hidden fields, I can count the number of matching pulls in the ticket and if the count matches one of the additional jackpots, I can spot it! A few more nested IF statements later, and it gives me a pretty results column that tells me just how much money I lost.

For the record, it was $66 in tickets that netted me $12 in winnings, a grand prize loss of $54, woo hoo!

To make this easier for everyone, I’ve extended the spreadsheet to 50 tickets and pumped in some dummy data so you can play around with the sheet. If you distribute it, please give credit where credit is due, but otherwise enjoy and good luck on the lotto! Hopefully Saturday it’ll be up to $650 Million and I’ll win! I promise $10,000 to anyone that comments and shares if I win the jackpot, up to 500 people!

Download the Spreadsheet