How To Analyse The Frequency Of Prime Numbers In Lotto Results

The frequency of prime numbers in random data provides a good introduction into using Excel and VBA in lotto research.

Prime Numbers, Excel and VBA

There are twelve primes between 1 and 40, so in most lotto results around 30% of numbers would be prime. One way of testing this theory is to create a data set of random numbers Swertres Hearing and use VBA code to count the frequency.

You can use any random number generator to create lines with 6 numbers between 1 and 40. In my experiment, I created 200 lines, but you can easily have as many as your computer memory will allow.

VBA code can create the numbers or you can try the random function in Excel:


Rand()*40

Creating A List Of Prime Numbers With VBA

First, we need to list the prime numbers in code. There are various techniques to do this, such as using arrays or a collection. I've decided to use the dictionary object as it has the flexibility which might be needed for more complex tasks in the weeks ahead.

First, we hard code the primes into an array so we can loop through them.


pr = Split("2,3,5,7,11,13,17,19,23,29,31,37", ",")

Now we add each prime to the dictionary.


Set dictTemp = CreateObject("Scripting.Dictionary")

For x = 0 To UBound(pr)

dictTemp.Add pr(x), pr(x)

Next

Counting The Frequency Of Primes In A Random Data Set

With our data set of numbers in an existing worksheet in Excel we're ready to loop through each line and count the frequency of prime numbers. What the code will achieve is create a separate column beside each line giving a count of the number of primes.

Our first five lines of data should look like this:


17 19 26 29 33 37

14 16 23 26 34 36

6 20 23 26 33 36

2 5 19 30 34 36

5 20 22 25 30 33

We'll name and then select the correct worksheet. It's good practice to set the name of the sheet because if our module gets complicated later on or we change the worksheet name it won't affect the code.


Set w1 = Worksheets("LottoLines")

w1.Activate

Next, we select all the numbers and count the rows and columns with the current region method. This technique enables us to move around the data set efficiently.


Set rg = Range("a1").CurrentRegion

rws = rg.Rows.Count

cols = rg.Columns.Count

Now the code can loop through each line and number to check whether it is a prime number.


For x = 1 To rws

p = 0

For y = 1 To cols

If the number is prime we can add it a temporary counter


n = rg.Rows(x).Columns(y)

If dictTemp.exists(n) Then

p = p + 1

End If

Next

With the line completed, we insert the count into a cell at the end of the line and move onto the next line.


rg.Rows(x).Columns(y) = p

Next

The completed output will be similar to the data below, with the final column showing the number of primes in each line.


17 19 26 29 33 37 4

14 16 23 26 34 36 1

6 20 23 26 33 36 1

2 5 19 30 34 36 3

5 20 22 25 30 33 1

16 21 22 29 33 37 2

Further Development Of The VBA Prime Numbers Code

With the count of primes in the random data set completed, you can now analyse the output further:

    Create a column calculating the average number of primes as the data size increases.
    Calculate a moving average of the frequency to determine any variance from the 30% average.
    Determine whether the primes are represented across each decile between and 1 and 40.

Summary

The research of prime numbers provides an ideal introduction into the study of lotto results. With a little knowledge of Excel and VBA it can be possible to research and identify lotto trends and patterns and improve your own lotto results.