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
32
PrintE-mailLink
Avatar
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.

    SergeM's avatar - slow icon.png
    Economy class
    Belgium
    Member #123700
    February 27, 2012
    4035 Posts
    Offline
    Posted: January 21, 2013, 1:49 pm - IP Logged

    Buy a program!

      RJOh's avatar - chipmunk
      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 * 
         
                   Evil Looking       

        RJOh's avatar - chipmunk
        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 * 
           
                     Evil Looking       

          Avatar

          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.

            SergeM's avatar - slow icon.png
            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.

              Avatar

              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
              sextet9240.0066%
              quint, single44,3520.3172%
              quad, pair166,3201.1894%
              quad, 2 single554,4003.9646%
              2 triple126,7200.9062%
              triple, pair, single2,851,20020.3893%
              triple, 3 single2,112,00015.1032%
              3 pair801,9005.7345%
              2 pair, 2 single5,346,00038.2299%
              pair, 4 single1,980,00014.1592%
              TOTAL13,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.

                RJOh's avatar - chipmunk
                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 * 
                   
                             Evil Looking       

                  SergeM's avatar - slow icon.png
                  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.

                    RJOh's avatar - chipmunk
                    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 * 
                       
                                 Evil Looking       

                      SergeM's avatar - slow icon.png
                      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.

                        Avatar

                        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

                          RJOh's avatar - chipmunk
                          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 * 
                             
                                       Evil Looking       

                            SergeM's avatar - slow icon.png
                            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-0247
                            02-2445
                            01-2443
                            01-1943
                            01-0942
                            01-0742
                            01-2040
                            01-1140
                            02-0840
                            01-0340
                            01-1839
                            01-0839
                            01-0538
                            02-1837
                            01-1737
                            02-1936
                            02-0936
                            08-2436
                            13-2036

                              RJOh's avatar - chipmunk
                              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-0247
                              02-2445
                              01-2443
                              01-1943
                              01-0942
                              01-0742
                              01-2040
                              01-1140
                              02-0840
                              01-0340
                              01-1839
                              01-0839
                              01-0538
                              02-1837
                              01-1737
                              02-1936
                              02-0936
                              08-2436
                              13-2036

                              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 * 
                                 
                                           Evil Looking