Identifying patterns in lotto results can be difficult but one pattern that is consistent is the frequency of prime numbers.

Prime numbers should make up around 30% of all lotto results and this article introduces the idea of moving averages and how they might help improve your own lotto odds.

A moving average is simply the average over a subset or period of data. It becomes a powerful tool when you consider subsequent averages which might indicate a move away or back to the mean.

**Prime Numbers And A Random Data Set**

The easiest way to test lotto ideas is to create your own random data set which should simulate a series of lotto results.

You need to create a table of random results and calculate how many prime numbers are in each line. Then, we can write some VBA code to calculate the average to see if there are any patterns in the numbers.

You should have a data table looking like this, where the final column is a count of how many primes are in each line.

18 19 26 29 30 33 2

2 9 12 22 35 39 1

6 9 12 19 20 31 2

5 16 20 22 27 29 2

18 20 23 28 31 39 2

3 19 20 27 31 35 3

Now we’re ready to write some VBA code which will calculate the averages.

**Lotto Results, VBA Code And Moving Averages**

As an example, in our data set above the result with a period of 2 lines would look like this.

18 19 26 29 30 33 2

2 9 12 22 35 39 1 1.5

6 9 12 19 20 31 2 1.5

5 16 20 22 27 29 2 2

18 20 23 28 31 39 2 2

3 19 20 27 31 35 3 2.5

In our VBA code, we’ll use a period of 10 to get some meaningful results.

The Excel formula to create an average over the first 10 lines is below, where “h” is the column which will hold your results.

=AVERAGE(h1:h10)

To calculate the next 10 period moving average you simply increment the row number.

=AVERAGE(h1:h10)

=AVERAGE(h2:h11)

In this example, just type the formula into row “h10” on the right hand column; then we’ll use VBA code to reiterate through the data set.

The code selects the first cell with the formula, calculates how many rows of data there are and then fills in the same formula through the whole data set.

range("h10").activate

i = ActiveCell.Offset(0, -1).End(xlDown).Row

Selection.AutoFill Destination:=Range("H10:h" & i)

**Development Of The Lotto Prime Numbers Moving Average**

When I ran the moving average module the results came out as expected, with moves away from the median on a regular basis. Some further development of the module might include the following:

- Creating a chart to enable visual identification of trends.
- Highlighting the lines of data that are above or below the global average.
- Determining maximum and minimum variations from the mean.

**Summary**

This article has introduced the concept of using VBA code to calculate averages of prime numbers in random data sets. The concept can easily be applied to lotto results and with a little creativity other idea and theories can be developed and explored.