Active Topics Memberlist Search Help  
Register Login 
Lotto Number Analysis Using Excel, Access and Visual Basic  
LottoPoster Forums : LOTTO PROBABILITY, COVERAGE AND PROGRAMMING : Lotto Number Analysis Using Excel, Access and Visual Basic 
Topic: PROBABILITY PRIMER OR REFRESHER BY LOTTO ANALYSIS  
Author  Message 
Colin F
Lotto Systems Tester Creator & Analyst To dream the impossible dream ... Joined: September 30 2004 Location: Australia Online Status: Offline Posts: 678 
Topic: PROBABILITY PRIMER OR REFRESHER BY LOTTO ANALYSIS Posted: November 01 2009 at 9:59pm 
Probability Primer or Refresher
using Lotto Number Analysis and VBA or VB code by Colin Fairbrother
Testing Environment
If you intend to seriously get involved in this subject then you need a programming test environment such as the debug window in Microsoft Excel, Access, Visual Basic, Visual C# or Visual C++. Excel is probably the most likely choice and the least known to get to the debug window  Click on Tools in the Menu bar then Macro  Visual Basic Editor or simply Alt + F11  once in that environment from the menu click View and make sure the Immediate Window is selected. I will give examples in VBA which applies to both Excel and Access and you will need to place the code below in a module to test it out using the debug window.
However, I strongly advise even if you are singularly competent in Excel or Access and haven't bought the Standard or higher version of Visual Studio, that you download and expand your expertise to include a modicum of familiarity with the free Visual Basic 2010 Express . The code given will work in VBA ie Access or Excel but for Visual Basic 2008 you will need to alter it slightly: 
Anyone with programming experience knows that there is usually more than one way of tackling a task so improvement suggestions are welcome. Probability and Odds As long as your lines are different any line is as good as another as far as getting 6 integers correct and this includes 1 2 3 4 5 6. I assure you that those who do not recognize this are in the loony brigade that tend to dominate this field of interest. So, Lotto number analysis with some intellectual rigor is more about maximizing your chances of getting the smaller prizes such as 3 integers correct.
Looking at 1 2 3 4 5 6 from the perspective of maximizing lower prize wins and playing a modest 20 lines with the integers in numerical order you may regard a line with 3 contiguous numerical order integers as being OK but not another line with 3 consecutive integers elsewhere in your set as the average ratio of consecutives for the set is exceeded. A line with 6 consecutive integers from this perspective may not be the best choice. To emphasize the usually astronomical odds for jackpot style games playing an unrealistic 1,000 lines your probability for getting 6 integers correct in Classic Lotto is 1000/13983816 or 0.0000715 and that of not getting 6 integers correct is 99.99999825 which are pretty well odds that you could safely stake your life on  certainly better than Russian roulette which is kaput 0.1666666 (16.66%) or reprieve 0.8333334 (83.33%). Independent Events
An experienced Excel user probably knows there is a Worksheet function Fact that can be used as in x = Application.WorksheetFunction.Fact(7) but this is not available as easily in Access or Visual Basic and some would see it as a bit cumbersome. To try out the following function press Alt F11 to bring up the Visual Basic Editor in Excel and make sure your layout is similar to this screen print. You may have to right click on VBA Project (Book 1) then insert  module to get a module in which to paste the code.
Function Fct(x As Byte) As Double
Dim i As Integer, f As Double If x < 0 Or x > 170 Then Fct = 0 Exit Function End If f = 1 For i = 2 To x f = f * i Next i Fct = f End Function Typing for example ? Fct(6) in the immediate window then enter will return 720. If using Visual Basic 2008 you could use a Console project and type after Sub Main: 
Console.WriteLine(Fct(6)) Console.ReadLine() Permutations Permutations with repetition or replacement. In the Pick 3 Lotto game we have 1,000 lines each of three digits which can be between 0 and 9 and the digits in one line can all be the same. The order is important for the highest payout. To calculate the number of permutations with replacement in a Pick 3 Lotto game we simply multiply 10 x 10 x 10 or 10^3. If instead of 3 segments we had 10 then the total permutations with replacement would be 10 x 10 x 10 x 10 x 10 x 10 x 10 x 10 x 10 x 10 = 10^10 = 10,000,000,000 ie 10 billion. Function for calculating Permutations with replacement:  Function Perm(n As Byte, k As Byte) As Long
Enter the following procedure code after the Factorial function and then type PermWR(9,3) (don't need to preface with ? in this case) in the immediate window to enumerate all the permutations with replacement for Lotto game Pick 3:  An example is the Australian addon game called LottoStrike where from a Pool of 45 integers your entry line with 4 integers must have 1 or more integers correct for the order drawn.
It should be obvious (if you've read the previous terms in this page) that the possibilities for the first 4 integers is given by 45 x 44 x 43 x 42 = 3,575,880. An obvious formula where Pl is Pool and Pk is Pick:  is Pl! / (Pl  Pk)! Function for calculating Permutations without repetition or replacement:  Function PermWoRCalc(n As Byte, k As Byte) As Long Procedure for Enumerating Permutations without Repetition or Replacement If you type PermWoR(45, 4) in the immediate window and then enter, the line with an index halfway through will be printed as well as the number of possibilities. 'Pl is Pool and Pk is Pick
Dim cnt As Long, l As Long Dim a As Byte, b As Byte, c As Byte, d As Byte Select Case Pk 'Pool 10 (0 to 9) Cnt is 5040 Case 4 'l = (Fct(Pl) / Fct(Pl  Pk)) / 2 For a = 0 To Pl  1 For b = 0 To Pl  1 If b = a Then GoTo BP4_2 For c = 0 To Pl  1 If c = b Or c = a Then GoTo BP4_3 For d = 0 To Pl  1 If d = c Or d = b Or d = a Then GoTo BP4_4 cnt = cnt + 1 'If cnt = l Then 'Debug.Print a & vbTab & b & vbTab & c & vbTab & d 'End If BP4_4: Next d BP4_3: Next c BP4_2: Next b Next a Case 3 'For Pl 10 (0 to 9) cnt is 720 ie unmatched For a = 0 To Pl  1 For b = 0 To Pl  1 If b = a Then GoTo BP3_2 For c = 0 To Pl  1 If c = b Or c = a Then GoTo BP3_3 cnt = cnt + 1 'Debug.Print a & vbTab & b & vbTab & c BP3_3: Next c BP3_2: Next b Next a End Select Debug.Print cnt End Sub Combinations For a given Pool and Pick the number of Combinations without repetition of the integers, where the order of the integers is not important, is a subset of the number of Permutations without repetition. A simple case where the Pool is 3 ie 0, 1, 2 and the Pick is 3 means we have 3 x 2 x 1 = 6 Permutations but only 1 combination which can present 6 ways but is usually given in numerical order as 0 1 2 but is still the same combination as 0 2 1 or 1 0 2 or 1 2 0 or 2 0 1 or 2 1 0. Combinations without repetition (or replacement) This applies to most Jackpot style games such as the classic Pool 49 Pick 6 game. Just like Factorial there is a worksheet function you could use Combin but VBA doesn't know anything about it unless you qualify the name as in Application.WorksheetFunction.Combin(49, 6). The same name can be used to create a function in VBA with no conflict. Function for calculating Combinations without replacement:  Function CombinWoR(n As Byte, k As Byte) As Double Dim cv As Double cv = n If n >= k AND n <101 Then For i = 1 to k  1 cv = cv * (ni) Next i cv = cv/Factorial(k) Else CombinWoR = 0 End If CombinWoR = cv 'Following works for smaller values 'CombinWoR = Fct(n) / Fct(k) / Fct(n  k) End Function Procedure for enumerating Combinations without repetition: Public Sub EnumCombWoR(Pl As Byte, Pk As Byte) Dim cnt As Long
Dim ary() As Byte
Dim a As Byte, b As Byte, c As Byte, d As Byte, e As Byte, f As Byte Dim Elements As Long Elements = CombinWoR(Pl, Pk)
Select Case Pk 'If Pl 49 and Pk 6 then cnt is 13,983,816 ReDim ary(1 To Elements, 1 To 6)
cnt = cnt + 1 ary(cnt, 1) = a Next f Debug.Print "Count of combinations in array: " & cnt & vbTab & ary(13983816, 6)
Case 3 'For Pl 10 (0 to 9) cnt is 120 ie unmatched 120 x 6 = 720 as in Pick 3
ReDim ary(1 To Elements, 1 To 3)
For a = 0 To Pl  3 'Debug.Print a & vbTab & b & vbTab & c Next c
End Select End Sub
Combinations with Repetition or Replacement: 
Function for calculating Combinations with replacement: 
Function CombinWR(n As Byte, k As Byte) As Long
Procedure for enumerating Combinations without Repetition or Replacement: 
Public Sub EnumCombWR(Pl As Byte, Pk As Byte) Dim cnt As Long
Select Case Pk 'Pl 49 and Pk 6 then cnt is 25,827,165 cnt = cnt + 1 Next f Next c End Select
End Sub
Calculating the Odds for the Prizes The code for calculating the Prize Odds is easily done now that the functions as detailed above are available to apply to the formulae. Taking a 6/49 Lotto game as an example the odds would be given by the following 
lblPrizeThree.Caption = "1 in " & Format(CombinWoR(Pl, Pk) / (CombinWoR(Pk, 3) * IIf(CombinWoR((Pl  Pk), 3) = 0, 1, CombinWoR((Pl  Pk), 3))) / tbxTickets.Value, "##,##0.00")
Providing an Excel UserForm Interface For Access or Visual Basic the natural interface is a form. In Excel a better choice than using controls on a worksheet when spreadsheet features are not needed, is a UserForm as shown below:  Colin Fairbrother


IP Logged  
Forum Jump 
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum 