How To Use Excel And VBA To Create A Graph Of Lotto Numbers

Posted on by admin

[ad_1]

All lotto researchers should have a method to quickly display data. For example, moving averages and median values can indicate trends or moves away from a pattern. This article will demonstrate how to use VBA to display a simple graph.

Excel Graphs, VBA And Lotto Results

Excel has many tools available for displaying graphs, but we are going to use the VBA programming environment to automate the task. Our code will create a simple chart of the median for each lotto number over a large series of results.

First, you will need some data. You can use real life lotto results or simply create some random numbers. A quick search on Google for a “random number generator” will give you plenty of information.

Your data should look like this:


18 19 26 29 30 33

2 9 12 22 35 39

6 9 12 19 20 31

5 16 20 22 27 29

You probably need a large data sample to get some quality results but we’re looking at the code needed rather than data issues.

The VBA Code To Generate A Graph

With the data saved in a worksheet, you need to select the worksheet and move the cursor to the first cell.


worksheet(1).activate

Range("a1").Activate

Next, we’ll work out the number of rows and columns in the data set and move the cursor to the first empty cell in column one. That’s where our median formula will go.

myRows = ActiveCell.End(xlDown).Row

myCols = ActiveCell.End(xlToRight).Column
rng = "a1:a" & myRows

ActiveCell.Offset(myRows, 0).Activate

ActiveCell = "=median(" & rng & ")"

With our median formula in place, we can copy the formula across the base of the other numbers.

 

rng = ActiveCell.Offset(0, myCols - 1).Address

Selection.AutoFill Destination:=Range(ActiveCell.Address & ":" & rng)

Creating A Graph From The Median Lotto Numbers

Now we have our median values, the last step is to create a graph of the results.

First, we select the range of the chart which is simply the cells containing the median formula.


rng = ActiveCell.Address & ":" & rng

Finally, we create the chart, set the source data and save it on the same worksheet.


Charts.Add

ActiveChart.ChartType = xlColumnClustered

ActiveChart.SetSourceData Source:=worksheets(1).Range(rng)

ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"

Further Development Of The VBA Lotto Graph Module

A graph of the median results for lotto numbers probably won’t be much help in improving your odds. But with a little thought you can probably extend the code to yield more complex results.

  • Averages within a range of numbers
  • Median results across each decile
  • Maximum and minimums within number ranges

Summary

This article has attempted to show how easy it can be to automate

Excel graphs. With a little knowledge of Excel and VBA, lotto analysts can rapidly improve their research results.

[ad_2]

Source by A. Lewis Gibson

admin

More Posts