Welcome Guest
Log In | Register )
You last visited December 8, 2016, 1:03 pm
All times shown are
Eastern Time (GMT-5:00)

# I have a question for you excel experts.

Topic closed. 18 replies. Last post 4 years ago by SergeM.

 Page 1 of 2
PrintE-mailLink
New Member
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.

Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: January 21, 2013, 1:49 pm - IP Logged

Buy a program!

mid-Ohio
United States
Member #9
March 24, 2001
19828 Posts
Offline
 Posted: January 28, 2013, 1:08 pm - IP Logged

Buy a program!

I did that by writing two simple programs.

* you don't need to buy more tickets, just buy a winning ticket *

mid-Ohio
United States
Member #9
March 24, 2001
19828 Posts
Offline
 Posted: January 28, 2013, 2:49 pm - IP Logged

I did that by writing two simple programs.

Program #1 turn combination  1-2-3-4-5-6 into 20 groups. (20 groups per combinations)

1-2-3   1-2-4   1-2-5   1-2-6   1-3-4   1-3-5   1-3-6   1-4-5   1-4-6   1-5-6
2-3-4   2-3-5   2-3-6   2-4-5   2-4-6   2-5-6
3-4-5   3-4-6   3-5-6
4-5-6

Sort all the groups by their numbers using the "sort" command

Program #2 record the different number groups and their size.

* you don't need to buy more tickets, just buy a winning ticket *

United States
Member #130795
July 25, 2012
80 Posts
Offline
 Posted: January 30, 2013, 4:00 am - 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.

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:

=COMBIN(9,4)*COMBIN(40,2) + 4*COMBIN(10,4)*COMBIN(39,2)

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.

Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: January 30, 2013, 2:22 pm - IP Logged

Program #1 turn combination  1-2-3-4-5-6 into 20 groups. (20 groups per combinations)

1-2-3   1-2-4   1-2-5   1-2-6   1-3-4   1-3-5   1-3-6   1-4-5   1-4-6   1-5-6
2-3-4   2-3-5   2-3-6   2-4-5   2-4-6   2-5-6
3-4-5   3-4-6   3-5-6
4-5-6

Sort all the groups by their numbers using the "sort" command

Program #2 record the different number groups and their size.

I wrote a similar program in VBA but have no use for it. It isn't really the type of job for Excel.

United States
Member #130795
July 25, 2012
80 Posts
Offline
 Posted: January 30, 2013, 2:57 pm - IP Logged

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:

=COMBIN(9,4)*COMBIN(40,2) + 4*COMBIN(10,4)*COMBIN(39,2)

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.

sextet
=COMBIN(9,6)+4*COMBIN(10,6)

quint, single
=COMBIN(9,5)*40 + 4*COMBIN(10,5)*39

quad, pair
=COMBIN(9,4)*COMBIN(10,2)*4 + 4*COMBIN(10,4)*(COMBIN(9,2)+COMBIN(10,2)*3)

quad, 2 single
=COMBIN(9,4)*10^2*COMBIN(4,2) + 4*COMBIN(10,4)*(9*10*3+10^2*COMBIN(3,2))

2 triple
=COMBIN(9,3)*COMBIN(10,3)*4 + COMBIN(10,3)^2*COMBIN(4,2)

triple, pair, single
=COMBIN(9,3)*COMBIN(10,2)*4*10*3 + 4*COMBIN(10,3)*(COMBIN(9,2)*10*3 + 3*COMBIN(10,2)*(9+10*2))

triple, 3 single
=COMBIN(9,3)*10^3*COMBIN(4,3) + 4*COMBIN(10,3)*(9*10^2*COMBIN(3,2) + 10^3)

3 pair
=COMBIN(9,2)*COMBIN(10,2)^2*COMBIN(4,2) + COMBIN(10,2)^3*COMBIN(4,3)

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
19828 Posts
Offline
 Posted: January 30, 2013, 4:46 pm - IP Logged

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

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
19828 Posts
Offline
 Posted: January 31, 2013, 2:12 pm - IP Logged

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

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

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

mid-Ohio
United States
Member #9
March 24, 2001
19828 Posts
Offline
 Posted: February 3, 2013, 2:41 pm - IP Logged

Program #1 turn combination  1-2-3-4-5-6 into 20 groups. (20 groups per combinations)

1-2-3   1-2-4   1-2-5   1-2-6   1-3-4   1-3-5   1-3-6   1-4-5   1-4-6   1-5-6
2-3-4   2-3-5   2-3-6   2-4-5   2-4-6   2-5-6
3-4-5   3-4-6   3-5-6
4-5-6

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.

06-22-29-38 = 4
16-34-42-49 = 3
16-26-37-47 = 3
12-14-29-36 = 3
09-13-21-32 = 3
07-23-25-28 = 3
17-36-46-48 = 3
06-30-31-39 = 3
18-19-24-38 = 3
06-10-28-42 = 3
03-07-29-40 = 3
01-09-40-46 = 2
01-12-16-46 = 2
01-13-15-35 = 2

* 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: February 3, 2013, 5:28 pm - IP Logged

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 …

mid-Ohio
United States
Member #9
March 24, 2001
19828 Posts
Offline
 Posted: February 3, 2013, 6:50 pm - IP Logged

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.

21-22 = 17
22-24 = 16
09-10 = 15
09-13 = 15
35-37 = 15
47-48 = 15
03-04 = 14
18-21 = 14
37-40 = 14
42-43 = 14
46-47 = 14
10-12 = 13
13-14 = 13
17-18 = 13
18-19 = 13
22-31 = 13
24-27 = 13
39-40 = 13
46-48 = 13
02-04 = 12
06-07 = 12
06-13 = 12
08-09 = 12

* you don't need to buy more tickets, just buy a winning ticket *

 Page 1 of 2