Youngstown, Oh United States
Member #73,255
April 9, 2009
17 Posts
Offline
I want to put together a formula that takes say a 6/49 lotto and point out say I want to find the most occuring quads, it finds them and show how many times each occurs. Also I'd like to break it down to show triads and pairs, and maybe even groups of 5 that may even occur. Thanks in advance.
United States
Member #130,789
July 25, 2012
80 Posts
Offline
Quote: Originally posted by MrJackpot on Jan 17, 2013
I want to put together a formula that takes say a 6/49 lotto and point out say I want to find the most occuring quads, it finds them and show how many times each occurs. Also I'd like to break it down to show triads and pairs, and maybe even groups of 5 that may even occur. Thanks in advance.
I assume that by "quad", you mean numbers in the same "teen". For example, any four of 1 through 9, or any four of 10 through 19, etc. Right?
For a particular quad, the following is the number of 6-tuples that contain that quad:
(a) For 1 through 9:
=COMBIN(49-9,2)
(b) For each of the other "teens" (e.g. 10 through 19)
=COMBIN(49-10,2)
Of course, 49-9 and 49-10 can be simplified. I write the formulas that way to show their derivation.
If you meant to ask: how many of the COMBIN(49,6) possible drawings contain any quad (not a particular one), the formula is:
The number of triads, pairs, etc can be computed in similar fashion. But the formulas are more complex, especially if we want to avoid double counting (we should). For example, some drawings contain both a triad and a pair.
I developed the formulas for the 5/56 Mega Millions game. I could develop them for the 6/49 game.
However, some of them might be too complex to include as text here. And the silly rules of this forum do not permit me (still a "new member") to post URLs such as pointers to uploaded files or to my email address.
I suggest that you post your inquiry in an appropriate Goolge Groups or Microsoft Community forum.
The number of triads, pairs, etc can be computed in similar fashion. But the formulas are more complex, especially if we want to avoid double counting (we should). For example, some drawings contain both a triad and a pair.
I developed the formulas for the 5/56 Mega Millions game. I could develop them for the 6/49 game.
However, some of them might be too complex to include as text here. And the silly rules of this forum do not permit me (still a "new member") to post URLs such as pointers to uploaded files or to my email address.
I suggest that you post your inquiry in an appropriate Goolge Groups or Microsoft Community forum.
I wrote: "I could develop them for the 6/49 game. However, some of them might be too complex to include as text here."
Actually, for the 6/49 game, the formulas are less complex because there is only one "short teen" -- the 1-through-9 group.
MrJackpot: "I want to put together a formula that takes say a 6/49 lotto and point out say I want to find the most occuring quads, it finds them and show how many times each occurs. Also I'd like to break it down to show triads and pairs, and maybe even groups of 5 that may even occur."
I wrote: "If you meant to ask: how many of the COMBIN(49,6) possible drawings contain any quad (not a particular one) ...."
In general, no quad is more likely than another, except that a quad from the 1-through-9 "teen" is less likely simply because there are fewer of them (126) than for a "full teen" like 10 through 19 (210).
I find it helpful to do the complete breakdown to be sure the formulas are correct.
2 pair, 2 single
=COMBIN(9,2)*COMBIN(10,2)*4*10^2*COMBIN(3,2) + COMBIN(10,2)^2*COMBIN(4,2)*(9*10*2 + 10^2)
pair, 4 single
=COMBIN(9,2)*10^4 + 4*COMBIN(10,2)*9*10^3
The results are:
Pattern
Count
%Total
sextet
924
0.0066%
quint, single
44,352
0.3172%
quad, pair
166,320
1.1894%
quad, 2 single
554,400
3.9646%
2 triple
126,720
0.9062%
triple, pair, single
2,851,200
20.3893%
triple, 3 single
2,112,000
15.1032%
3 pair
801,900
5.7345%
2 pair, 2 single
5,346,000
38.2299%
pair, 4 single
1,980,000
14.1592%
TOTAL
13,983,816
COMBIN(49,6)
13,983,816
It is tempting to think: "I will generate combinations with only the form 2 pair 2 single because they occur 38% of the time". (Just to keep the example simple. Actually, we might also include other frequent forms like triple pair single. Together, they occur more than 58% of the time.)
But the conditional probability is (1/5,346,000)*(5,346,000/13,983,816). That is, 1-in-5mil for the 2p2s form, but only if that pattern is drawn, which is 5mil-in-14mil. So cancelling terms algebraically, the effective probability is 1/13,983,816, which is the same as any random selection from the COMBIN(49,6) combinations.
That should come as no surprise. We cannot improve the random probability, other than buying more different combinations. But even so, our chances are a multiple of 1/13,983,816. (Unless you believe in the cosmic mumbo-jumbo that passes for "mathematical" systems in this forum.)
Nevertheless, it might make us feel good to select combinations of the forms that appear more often.
mid-Ohio United States
Member #9
March 24, 2001
20,272 Posts
Offline
Quote: Originally posted by SergeM on Jan 30, 2013
I wrote a similar program in VBA but have no use for it. It isn't really the type of job for Excel.
It not surprising you've already written a similar program and had no use for it. Has anyone at LP ever had an idea that you hadn't tried already and found useless?
* you don't need to buy every combination, just the winning ones *
Economy class Belgium
Member #123,694
February 27, 2012
4,035 Posts
Offline
Quote: Originally posted by RJOh on Jan 30, 2013
It not surprising you've already written a similar program and had no use for it. Has anyone at LP ever had an idea that you hadn't tried already and found useless?
Why?
Technically this is better done in C, C++ or other I think. This is not typical Excel stuff.
I used the function for several posts on LP and nothing else.
mid-Ohio United States
Member #9
March 24, 2001
20,272 Posts
Offline
Quote: Originally posted by SergeM on Jan 30, 2013
Why?
Technically this is better done in C, C++ or other I think. This is not typical Excel stuff.
I used the function for several posts on LP and nothing else.
Just wondering why you waited until after I suggested a simple solution that could be done using any computer language,perhaps even Small Basic which Microsoft offers freely.
* you don't need to buy every combination, just the winning ones *
Economy class Belgium
Member #123,694
February 27, 2012
4,035 Posts
Offline
Quote: Originally posted by RJOh on Jan 31, 2013
Just wondering why you waited until after I suggested a simple solution that could be done using any computer language,perhaps even Small Basic which Microsoft offers freely.
On my own I found several ways to program combinations. It is all experimental. I will translate my VBA codes to java and to other like VB later.
United States
Member #130,789
July 25, 2012
80 Posts
Offline
Quote: Originally posted by MrJackpot on Jan 17, 2013
I want to put together a formula that takes say a 6/49 lotto and point out say I want to find the most occuring quads, it finds them and show how many times each occurs. Also I'd like to break it down to show triads and pairs, and maybe even groups of 5 that may even occur. Thanks in advance.
I wrote: "I assume that by "quad", you mean numbers in the same "teen". For example, any four of 1 through 9, or any four of 10 through 19, etc. Right?"
Wrong! After some research, I see that you probably mean any 4 numbers.
Sorry for the lengthly misdirection previously. However, perhaps it will be useful to someone else in the future.
We really cannot use Excel formulas per se to count the frequency of quads appearing in historical drawings. At the very least, it would require a very large workbook since there are 211,876 quads in a 6/49 game -- COMBIN(49,4).
It would be better to use VBA. For example, see the VBA procedure below.
It assumes that the historical drawings are in the worksheet "data" in columns C:H starting in row 1. For each drawing, the numbers should be in ascending order left to right.
It also assumes the existence of the worksheet "quads". The VBA procedure will write the results into columns A:E. Afterwards, you can sort those columns as you wish.
The VBA procedure below is not elegant. It is intended to be simple to understand. Nevertheless, it is sufficiently efficient, IMHO. Using the nearly 33-year data (3117 drawings!) for the NJ Pick Six Lotto (the 6/49 game), the VBA procedure runs in about 6 sec using Excel 2010 on my very old (read: slow) laptop. YMMV.
(Sorry for including the lengthy text of the VBA procedure here instead of simply pointing to a downloadable file. As I am still a "new member", the silly rules of this forum do not permit me to post real URLs; and I cannot risk experimenting anymore with veiled forms.
-----
Option Explicit
Sub countQuads()
Const ntbl As Long = 211876 ' combin(49,4)
Dim tbl(1 To ntbl, 1 To 2) As Long
Dim v As Variant
Dim nv As Long, i As Long, j As Long
Dim n1 As Long, n2 As Long, n3 As Long, n4 As Long
Dim k As Long, k1 As Long, k2 As Long, k3 As Long
Dim m As Long, lo As Long, hi As Long
Dim r As Long
Dim s As String
Dim st As Single, et As Single
' requires sheets "data" and "quads".
'
' "data" contains historical drawings in columns C:H
' starting in row 1. for each drawing, numbers must
' be in ascending order from left to right.
'
' "quads" will contain results in columns A:E.
st = Timer
Sheets("data").Activate
v = Range("c1", Range("h1").End(xlDown))
nv = UBound(v, 1)
' build quad lookup table
i = 0
For n1 = 1 To 46
k1 = n1 * 1000000
For n2 = n1 + 1 To 47
k2 = n2 * 10000
For n3 = n2 + 1 To 48
k3 = n3 * 100
For n4 = n3 + 1 To 49
i = i + 1
k = k1 + k2 + k3 + n4
tbl(i, 1) = k
Next n4, n3, n2, n1
DoEvents
' determine quads
For i = 1 To nv
For n1 = 1 To 3
k1 = v(i, n1) * 1000000
For n2 = n1 + 1 To 4
k2 = v(i, n2) * 10000
For n3 = n2 + 1 To 5
k3 = v(i, n3) * 100
For n4 = n3 + 1 To 6
k = k1 + k2 + k3 + v(i, n4)
' binary search of tbl
lo = 1: hi = ntbl
Do
m = Int((lo + hi) / 2)
If k = tbl(m, 1) Then Exit Do
If k < tbl(m, 1) Then hi = m - 1 _
Else lo = m + 1
Loop
tbl(m, 2) = tbl(m, 2) + 1
Next n4, n3, n2, n1
If i Mod 100 = 0 Then DoEvents
Next i
' write quads
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Sheets("quads").Activate
Columns("a:e").Clear
Range("a1") = "count"
Range("b1") = "quad..."
r = 1
For i = 1 To ntbl
If tbl(i, 2) <> 0 Then
r = r + 1
Cells(r, 1) = tbl(i, 2)
k = tbl(i, 1)
For j = 5 To 2 Step -1
Cells(r, j) = k Mod 100
k = Int(k / 100)
Next
End If
Next
Columns("a:e").AutoFit
s = Format(ntbl - r + 1, "#,##0") & " of " & Format(ntbl, "#,##0")
Cells(r + 1, 1) = 0
Cells(r + 1, 2) = s
et = Timer
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
MsgBox s & " = 0" & _
vbNewLine & Format(et - st, "0.000000") & " sec"
End Sub
Sort all the groups by their numbers using the "sort" command
Program #2 record the different number groups and their size.
I did that for combinations of fours in the Ohio Classic Lotto which is a 6/49 game that has had 945 drawings. As of its last drawing, 13,723 of the possible 211,876 combinations of fours had come up so far. The following is part of a list of those combinations.
mid-Ohio United States
Member #9
March 24, 2001
20,272 Posts
Offline
Quote: Originally posted by SergeM on Feb 3, 2013
An Excel version:
The most frequent couples
01-02
47
02-24
45
01-24
43
01-19
43
01-09
42
01-07
42
01-20
40
01-11
40
02-08
40
01-03
40
01-18
39
01-08
39
01-05
38
02-18
37
01-17
37
02-19
36
02-09
36
08-24
36
13-20
36
…
For the Ohio Classic Lotto the follow in part of the list for two number combinations and all 1176 possible combinations of twos have come up aleast twice.
