Use COUNTIF function and sorting to analyze the PowerBall Playout


Use COUNTIF function and sorting to analyze the PowerBall Playout

powerball analysis
msofficeshare logo

File to download: Powerball Analysis.xlsx

Related article: Lottery Numbers Generator

Even in a random world, something in somewhere at some time is happening more frequently than the others. The occurrence may even out in a very long run, but fluctuations do happen in a lesser period of time. For example, you may set your MP3 player with random shuffling, but somehow some songs just happen to occur more frequently than the others. I am curious to see if PowerBall shows such uneven occurrence. I downloaded almost 5 years’ data (2/3/2010 – 12/24/2014) from this website ( and run an analysis. I am surprised that some numbers did occur way more frequently than the others. The #29 red ball (Power ball) happened way more often than the other numbers (25 occurrence out of 511 total occurrence while #37 only occurred 3 times. Could it be just random or attribute to the way the balls are placed before they are dumped into the machine ? Could a longer period of time even out the occurrence ? I’ll leave that to your decision.

The data I downloaded from the website are shown in the following illustration. White balls are shown in 5 columns (from column D to H) and the red ball (Powerball) is in column I. Since the white balls are listed in 5 columns. It is not easy to use either pivot table or filters to slice and dice the data. So I decided to use the COUNTIF function to do the trick.

pic1 powerball analysis
pic2 powerball analysis

Syntax: COUNTIF(range, criteria).

The data range is the data that I downloaded. The white balls and red balls must be separated. So the data range for the white ball is only from column D to column H. The criteria is the individual possible number (from 1 to 59 white ball). Please make the data range absolute by inserting dollar sign or you can name such range so that you can copy the first formula to the last number. Afterwards, copy the data and paste-value them to any place in your worksheet as desired, then sort the data. Please repeat the same process for the red ball.

Please note that there are limitations of this analysis.

  • There is no guarantee that you can make money on this analysis. History may or may not repeat itself even there is a tendency it may.
  • You may “invest” more than you can get back. Please remember the odds of winning lotteries are always against you. Unless you are lucky enough to win a jackpot, it is more likely you will spend more money to buy the lottery tickets than profit from winning.
  • This type of analysis may reveal certain patterns of movement or occurrences, but it rarely pinpoints the exact time when it will happen, if it evens happen at all. For example, everyone knows making money in the stock market is to buy low sell high, but who knows whether today’s low is the lowest point or just another high point for an even lower prices to come.

Good luck and enjoy the analysis. Playing with numbers are fun.

Disclaimer: This article is not intended to promote or endorse gambling. It is purely for fun and for scientific curiosity.

More Posts

Excel Vlookup Function

The VLOOKUP function in Excel is a powerful tool that allows you to search for a specific value in a table and return a corresponding