Youngstown, Oh United States Member #73257 April 9, 2009 16 Posts Offline

Posted: January 17, 2013, 1:39 pm - IP Logged

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 #130795 July 25, 2012 80 Posts Offline

Posted: January 30, 2013, 4:00 am - IP Logged

Quote: Originally posted by MrJackpot on January 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 20017 Posts Offline

Posted: January 30, 2013, 4:46 pm - IP Logged

Quote: Originally posted by SergeM on January 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 more tickets, just buy a winning ticket *

Economy class Belgium Member #123700 February 27, 2012 4035 Posts Offline

Posted: January 30, 2013, 7:29 pm - IP Logged

Quote: Originally posted by RJOh on January 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 20017 Posts Offline

Posted: January 31, 2013, 2:12 pm - IP Logged

Quote: Originally posted by SergeM on January 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 more tickets, just buy a winning ticket *

Economy class Belgium Member #123700 February 27, 2012 4035 Posts Offline

Posted: January 31, 2013, 2:50 pm - IP Logged

Quote: Originally posted by RJOh on January 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 #130795 July 25, 2012 80 Posts Offline

Posted: February 1, 2013, 12:12 am - IP Logged

Quote: Originally posted by MrJackpot on January 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 20017 Posts Offline

Posted: February 3, 2013, 6:50 pm - IP Logged

Quote: Originally posted by SergeM on February 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.