Welcome Guest
Log In | Register )
You last visited December 4, 2016, 9:15 am
All times shown are
Eastern Time (GMT-5:00)

excel formula ? to show what number came out after a winning number

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

Page 1 of 2
51
PrintE-mailLink
Lucky5of5's avatar - Lottery-038.jpg
Illinois
United States
Member #129173
June 11, 2012
87 Posts
Offline
Posted: June 21, 2012, 11:32 pm - IP Logged

For Pick 3 or Pick 4, each digit of the winning number is in its own column.
For Pick 5 game, each of the five numbers is in its own column.
Let's say with a Pick 5 game, #10 fell in tonight's game in the 2nd numerical position (2nd column). What I want to find is what number came out after #10 in the 2nd position/column of all previous games. Is there a formula I can type in to give me this info?
All I know how to do right now is to use the AutoFilter to find the dates that #10 fell in the 2nd position/column, copy the dates, then look at the whole list to see what numbers fell "after" the dates that #10 was drawn. I'm wondering if there's an easier way to arrive at the info I want.

    SergeM's avatar - slow icon.png
    Economy class
    Belgium
    Member #123700
    February 27, 2012
    4035 Posts
    Offline
    Posted: June 22, 2012, 6:51 am - IP Logged

    There is a more complicated way.

      SergeM's avatar - slow icon.png
      Economy class
      Belgium
      Member #123700
      February 27, 2012
      4035 Posts
      Offline
      Posted: June 22, 2012, 7:26 am - IP Logged

      Let's say with a Pick 5 game, #10 fell in tonight's game in the 2nd numerical position (2nd column). What I want to find is what number came out after #10 in the 2nd position/column of all previous games.

      Is there a formula I can type in to give me this info?

      All I know how to do right now is to use the AutoFilter to find the dates that #10 fell in the 2nd position/column, copy the dates, then look at the whole list to see what numbers fell "after" the dates that #10 was drawn. I'm wondering if there's an easier way to arrive at the info I want.

      Using worksheetfunctions for pick 5:

      Use one column next to the data for the output, enter a function to return the second number if the previous datarow contained number 10 at ordinal position 2.

        SergeM's avatar - slow icon.png
        Economy class
        Belgium
        Member #123700
        February 27, 2012
        4035 Posts
        Offline
        Posted: June 22, 2012, 10:55 am - IP Logged

        PICK3 BE

        COUNT: TRAILER T/F BY LINE

        N\T0123456789
        0215238197212209215204214198218
        1217220246239221200223229231230
        2215220191226230208207206203239
        3201220203222230212225220230230
        4204251198208190239212229229232
        5209230194212200192222214219205
        6175210226213221215224215221245
        7221231234209225206225225233223
        8224209224229230202227235230201
        9245239214229230205209227214218

        WITH VBA

        You don't sum up dates, so that would be a list on the other side.

        If you specialize: number occurs 1 time, trailer occurs 1 time, then you get a different result.

        N\T0123456789
        0173197145174170171163162161179
        1173192191189186161185177186192
        2156170143175176160154163153186
        3168178151171178167187179179183
        4175203159164146189159184192190
        5173182149167156150187172189161
        6133172174175183173177167184199
        7180181174168191166185176182188
        8186175175184184169186197187161
        9187198163184183167175180171174

        Indeed you find strange stuff when you define how many times the number and trailer should occur.
        So I found here for number 3 times and trailer 3 times the following table.

        N\T0123456789
        00000000020
        10000000000
        20000000000
        30000000000
        40000000000
        50000000000
        60000000000
        70000000000
        80000000000
        90000000000

        I did a test and it is true. It happened:

        1/12/2007
        20/06/2012

        So I don't know if your post is related to the results of Pick 3 of Belgium.

        N*2 AND T*2

        N\T0123456789
        05230013221
        11241212262
        21344111304
        32314311412
        41410121012
        52121323103
        63163113201
        72154014042
        81132201252
        90142100411

        And so on.


        I suppose that your view at it is sequential.

          Avatar

          Belgium
          Member #128218
          May 19, 2012
          96 Posts
          Offline
          Posted: June 22, 2012, 2:24 pm - IP Logged

          Topic sounds nice... Smile

            SergeM's avatar - slow icon.png
            Economy class
            Belgium
            Member #123700
            February 27, 2012
            4035 Posts
            Offline
            Posted: June 22, 2012, 2:51 pm - IP Logged

            Topic sounds nice... Smile

            My data input above was wrong above, but the functions work. It was my exercise file.

            I need a break from this.

              CARBOB's avatar - FL LOTTERY_LOGO.png
              ORLANDO, FLORIDA
              United States
              Member #4924
              June 3, 2004
              5893 Posts
              Offline
              Posted: June 22, 2012, 4:01 pm - IP Logged

              Is this what you are looking for?  LDP1= Last draw p1; NDP= next draw p1' you will have to do this for each postion.

              formula in Hits column= change the ranges for your workbook.

              =SUMPRODUCT(--('HOT-COLD'!$B$8:$B$4000=$AF$7),--('HOT-COLD'!$B$9:$B$4001=$AG7))

              formula for  skips, enter as array formula, Ctrl-Shift-Enter!!!

              =CHOOSE(IF(N($AH7),IF(COLUMNS($AM7:AM7)=1, 1, IF(COLUMNS($AM7:AM7)<=$AH7,2,3)),3),SMALL(IF('HOT-COLD'!$B$8:$B$4000&"@"&'HOT-COLD'!$B$9:$B$4001=$AF$7&"@"&$AG7,ROW('HOT-COLD'!$B$8:$B$4000)-ROW('HOT-COLD'!$B$8)),1),SUM(SMALL(IF('HOT-COLD'!$B$8:$B$4000&"@"&'HOT-COLD'!$B$9:$B$4001=$AF$7&"@"&$AG7,ROW('HOT-COLD'!$B$8:$B$4000)),{1,0}+COLUMNS($AM7:AM7)-1)*{1,-1}),"")

              LDP1NDP1HITSMEDMAXAVGDUESKIPS
              11923815443.412106
               3721828655.47154
               2684620558.7400
               4604324566.57169
               5434554992.88021
               6407137199.851128
               734109386117.471107
               833103315121.031150
               927123356147.931216
               122493741166.421219
               112397740173.652276
               1022141656181.552417
               1516127558249.631322
               13102121192399.4031192
               1673541849570.570191
               1464681373665.671452
               1863301270665.67028
               1755871805798.8021805
               1952331256798.80089
               23364421711331.330644
               202139018401997.0011840
               2217477473994.000747
               271205220523994.00 2052
               241157415743994.00 1574
               210#NUM!0#DIV/0!  
               260#NUM!0#DIV/0!  
               280#NUM!0#DIV/0!  
               250#NUM!0#DIV/0!  
               290#NUM!0#DIV/0!  
               300#NUM!0#DIV/0!  
               310#NUM!0#DIV/0!  
               320#NUM!0#DIV/0!  
               330#NUM!0#DIV/0!  
               340#NUM!0#DIV/0!  
               350#NUM!0#DIV/0!  
               360#NUM!0#DIV/0!  
                Lucky5of5's avatar - Lottery-038.jpg
                Illinois
                United States
                Member #129173
                June 11, 2012
                87 Posts
                Offline
                Posted: June 22, 2012, 4:03 pm - IP Logged

                Using just a short span of winning numbers here, rather than all of them:

                5/31/12 ==  5    10    13     26    34
                6/1/12  ==   2    13    14    29     33
                6/2/12  ==   1     4     35    36     39
                6/3/12  ==   1     2      7      8     28
                6/4/12  == 13    16    17    19     34
                6/5/12  ==   5    12    32    33     39
                6/6/12  == 13    20    25     31    38
                6/7/12  == 11    15    16    21     38
                6/8/12  ==  3      9    12     27     35
                6/9/12  ==  1      3     7     17      20
                6/10/12 ==  7    10    20    24     38
                6/11/12 ==  7     8      9     11     35
                6/12/12 == 12    17    18    31     36
                6/13/12 ==   2    24    26    28     29
                6/14/12 ==   2     9     10    11     26
                6/15/12 ==   1    11    20    24     33
                6/16/12 ==   5    13    16    22     27
                6/17/12 ==   1    18    23    24     29
                6/18/12 ==   3     7    19     21     36
                6/19/12 ==   2     6     7     11      24
                6/20/12 ==   2     6    23    28      35
                6/21/12 ==   1    11    13    20     27


                #10 fell  in the second position on 5/31/12 and on 6/10/12.  Numbers 13 and 8 came out after the #10.

                So, in doing what you suggest, 13 and 8 (and more when I do the whole set of winning #s) will show in the results?

                Thanks.


                  United States
                  Member #124493
                  March 14, 2012
                  7023 Posts
                  Offline
                  Posted: June 22, 2012, 4:27 pm - IP Logged

                  Is this what you are looking for?  LDP1= Last draw p1; NDP= next draw p1' you will have to do this for each postion.

                  formula in Hits column= change the ranges for your workbook.

                  =SUMPRODUCT(--('HOT-COLD'!$B$8:$B$4000=$AF$7),--('HOT-COLD'!$B$9:$B$4001=$AG7))

                  formula for  skips, enter as array formula, Ctrl-Shift-Enter!!!

                  =CHOOSE(IF(N($AH7),IF(COLUMNS($AM7:AM7)=1, 1, IF(COLUMNS($AM7:AM7)<=$AH7,2,3)),3),SMALL(IF('HOT-COLD'!$B$8:$B$4000&"@"&'HOT-COLD'!$B$9:$B$4001=$AF$7&"@"&$AG7,ROW('HOT-COLD'!$B$8:$B$4000)-ROW('HOT-COLD'!$B$8)),1),SUM(SMALL(IF('HOT-COLD'!$B$8:$B$4000&"@"&'HOT-COLD'!$B$9:$B$4001=$AF$7&"@"&$AG7,ROW('HOT-COLD'!$B$8:$B$4000)),{1,0}+COLUMNS($AM7:AM7)-1)*{1,-1}),"")

                  LDP1NDP1HITSMEDMAXAVGDUESKIPS
                  11923815443.412106
                   3721828655.47154
                   2684620558.7400
                   4604324566.57169
                   5434554992.88021
                   6407137199.851128
                   734109386117.471107
                   833103315121.031150
                   927123356147.931216
                   122493741166.421219
                   112397740173.652276
                   1022141656181.552417
                   1516127558249.631322
                   13102121192399.4031192
                   1673541849570.570191
                   1464681373665.671452
                   1863301270665.67028
                   1755871805798.8021805
                   1952331256798.80089
                   23364421711331.330644
                   202139018401997.0011840
                   2217477473994.000747
                   271205220523994.00 2052
                   241157415743994.00 1574
                   210#NUM!0#DIV/0!  
                   260#NUM!0#DIV/0!  
                   280#NUM!0#DIV/0!  
                   250#NUM!0#DIV/0!  
                   290#NUM!0#DIV/0!  
                   300#NUM!0#DIV/0!  
                   310#NUM!0#DIV/0!  
                   320#NUM!0#DIV/0!  
                   330#NUM!0#DIV/0!  
                   340#NUM!0#DIV/0!  
                   350#NUM!0#DIV/0!  
                   360#NUM!0#DIV/0!  

                  good gosh carbob!!!EekShiftyOogle

                    CARBOB's avatar - FL LOTTERY_LOGO.png
                    ORLANDO, FLORIDA
                    United States
                    Member #4924
                    June 3, 2004
                    5893 Posts
                    Offline
                    Posted: June 22, 2012, 4:35 pm - IP Logged

                    Using just a short span of winning numbers here, rather than all of them:

                    5/31/12 ==  5    10    13     26    34
                    6/1/12  ==   2    13    14    29     33
                    6/2/12  ==   1     4     35    36     39
                    6/3/12  ==   1     2      7      8     28
                    6/4/12  == 13    16    17    19     34
                    6/5/12  ==   5    12    32    33     39
                    6/6/12  == 13    20    25     31    38
                    6/7/12  == 11    15    16    21     38
                    6/8/12  ==  3      9    12     27     35
                    6/9/12  ==  1      3     7     17      20
                    6/10/12 ==  7    10    20    24     38
                    6/11/12 ==  7     8      9     11     35
                    6/12/12 == 12    17    18    31     36
                    6/13/12 ==   2    24    26    28     29
                    6/14/12 ==   2     9     10    11     26
                    6/15/12 ==   1    11    20    24     33
                    6/16/12 ==   5    13    16    22     27
                    6/17/12 ==   1    18    23    24     29
                    6/18/12 ==   3     7    19     21     36
                    6/19/12 ==   2     6     7     11      24
                    6/20/12 ==   2     6    23    28      35
                    6/21/12 ==   1    11    13    20     27


                    #10 fell  in the second position on 5/31/12 and on 6/10/12.  Numbers 13 and 8 came out after the #10.

                    So, in doing what you suggest, 13 and 8 (and more when I do the whole set of winning #s) will show in the results?

                    Thanks.

                    You will need to sort the draws, new to old, for formulas to work correct.

                      SergeM's avatar - slow icon.png
                      Economy class
                      Belgium
                      Member #123700
                      February 27, 2012
                      4035 Posts
                      Offline
                      Posted: June 22, 2012, 4:40 pm - IP Logged
                      NLAST DIGITGROUPO/EFREQ.SKIPSACTUALHIGH SKIPDUE H.S.
                      110ODD62,0,5,5,1,30-5           - 
                      220EVEN61,1,9,0,4,0-1-9       0,11
                      330ODD38,0,8-3-8       0,38
                      440EVEN12-19-19       1,00
                      550ODD34,10-5-10       0,50
                      660EVEN219,0-1-19       0,05
                      770ODD63,5,0,0,6,0-2-6       0,33
                      880EVEN23,7-10-10       1,00
                      990ODD38,2,2-7-8       0,88
                      10010EVEN39,3-7-9       0,78
                      11110ODD67,3,2,0,3,10-7           - 
                      12210EVEN35,2,3-9-9       1,00
                      13310ODD60,2,1,9,40-9           - 
                      14410EVEN11-20-20       1,00
                      15510ODD17-14-14       1,00
                      16610EVEN34,2,8-5-8       0,63
                      17710ODD34,4,2-9-9       1,00
                      18810EVEN212,4-4-12       0,33
                      19910ODD24,13-3-13       0,23
                      20020EVEN56,2,0,4,50-6           - 
                      21120ODD27,10-3-10       0,30
                      22220EVEN116-5-16       0,31
                      23320ODD217,2-1-17       0,06
                      24420EVEN510,2,1,1,1-2-10       0,20
                      25520ODD16-15-15       1,00
                      26620EVEN312,0-7-12       0,58
                      27720ODD38,7,40-8           - 
                      28820EVEN33,9,6-1-9       0,11
                      29920ODD31,11,3-4-11       0,36
                      30030EVEN0NIETNIET
                      31130ODD26,5-9-9       1,00
                      32230EVEN15-16-16       1,00
                      33330ODD31,3,9-6-9       0,67
                      34430EVEN23-17-17       1,00
                      35530ODD42,5,2,8-1-8       0,13
                      36630EVEN32,9,5-3-9       0,33
                      37730ODD0NIETNIET
                      38830EVEN36,0,2-11-11       1,00
                      39930ODD22,2-16-16       1,00

                        SergeM's avatar - slow icon.png
                        Economy class
                        Belgium
                        Member #123700
                        February 27, 2012
                        4035 Posts
                        Offline
                        Posted: June 22, 2012, 4:46 pm - IP Logged
                        123456789101112131415161718192021222324252627282930313233343536373839
                        NNNN0NNNN0NN0NNNNNNNNNNNN0NNNNNNN0NNNNN
                        N0NN-1NNNN-1NN00NNNNNNNNNNN-1NN0NNN0-1NNNNN
                        0-1N0-2NNNN-2NN-1-1NNNNNNNNNNN-2NN-1NNN-1-200NN0
                        00N-1-3N00N-3NN-2-2NNNNNNNNNNN-3N0-2NNN-2-3-1-1NN-1
                        -1-1N-2-4N-1-1N-4NN0-3N00N0NNNNNN-4N-1-3NNN-30-2-2NN-2
                        -2-2N-30N-2-2N-5N0-1-4N-1-1N-1NNNNNN-5N-2-4NN00-1-3-3NN0
                        -3-3N-4-1N-3-3N-6N-10-5N-2-2N-20NNNN0-6N-3-5N0-1-1-2-4-4N0-1
                        -4-4N-5-2N-4-4N-70-2-1-600-3N-3-10NNN-1-7N-4-6N-1-2-2-3-5-5N0-2
                        -5-50-6-3N-5-50-8-10-2-7-1-1-4N-4-2-1NNN-2-80-5-7N-2-3-3-40-6N-1-3
                        0-60-7-4N0-6-1-9-2-1-3-8-2-20N-50-2NNN-3-9-1-6-8N-3-4-4-5-1-7N-2-4
                        -1-7-1-8-5N0-7-20-3-2-4-9-3-3-1N-60-3NN0-4-10-2-7-9N-4-5-5-6-2-8N0-5
                        -2-8-2-9-6N000-10-3-5-10-4-4-2N-7-1-4NN-1-5-11-3-8-10N-5-6-6-70-9N-1-6
                        -3-9-3-10-7N-1-1-1-2-10-6-11-5-500-8-2-5NN-2-6-12-4-9-11N0-7-7-8-10N-2-7
                        -40-4-11-8N-2-2-2-3-2-1-7-12-6-6-1-1-9-3-6NN0-70-500N-1-8-8-9-2-1N-3-8
                        -50-5-12-9N-3-3000-2-8-13-7-7-2-2-10-4-7NN-1-80-6-1-1N-2-9-9-10-3-2N-4-9
                        0-1-6-13-10N-4-4-1-10-3-9-14-8-8-3-3-110-8NN0-9-1-7-2-2N-3-100-11-4-3N-5-10
                        -1-2-7-140N-5-5-2-2-1-40-15-90-4-4-12-1-90N-1-10-20-3-3N-4-11-1-12-5-4N-6-11
                        0-3-8-15-1N-6-6-3-3-2-5-1-16-10-1-50-13-2-10-100-11-3-1-40N-5-12-2-13-6-5N-7-12
                        -1-40-16-2N0-7-4-4-3-6-2-17-11-2-6-10-30-2-1-1-12-4-2-5-1N-6-13-3-14-70N-8-13
                        -20-1-17-300-8-5-50-7-3-18-12-3-7-2-1-4-1-3-20-13-5-3-6-2N-7-14-4-15-8-1N-9-14
                        -30-2-18-40-1-9-6-6-1-8-4-19-13-4-8-3-2-5-2-40-1-14-6-40-3N-8-15-5-160-2N-10-15
                        0-1-3-19-5-1-2-10-7-70-90-20-14-5-9-4-30-3-5-1-2-15-70-1-4N-9-16-6-17-1-3N-11-16

                          SergeM's avatar - slow icon.png
                          Economy class
                          Belgium
                          Member #123700
                          February 27, 2012
                          4035 Posts
                          Offline
                          Posted: June 22, 2012, 4:49 pm - IP Logged
                          s2019181716151413121110987654321G.O.
                          1_______________055130
                          2_______________190401
                          3__________________083
                          4____________________19
                          5__________________4105
                          6___________________01
                          7_______________500602
                          8___________________710
                          9__________________227
                          10__________________937
                          11_______________320310
                          12__________________239
                          13_______________021940
                          14____________________20
                          15____________________14
                          16__________________285
                          17__________________429
                          18___________________44
                          19___________________133
                          20________________20450
                          21___________________103
                          22____________________5
                          23___________________21
                          24________________21112
                          25____________________15
                          26__________________1207
                          27__________________740
                          28__________________961
                          29__________________1134
                          30____________________NIET
                          31___________________59
                          32____________________16
                          33__________________396
                          34___________________317
                          35_________________5281
                          36__________________953
                          37____________________NIET
                          38__________________0211
                          39___________________216

                            Lucky5of5's avatar - Lottery-038.jpg
                            Illinois
                            United States
                            Member #129173
                            June 11, 2012
                            87 Posts
                            Offline
                            Posted: June 23, 2012, 4:47 am - IP Logged

                            Gee, I'm sorry, but none of these suggestions sounds like the info I'm looking for. I'm not interested in pick 3, skips, hits, avg, med, max, due, odd/even, or frequency. Let me rephrase what I'm wanting, hope that Excel can perform it, and someone knows how to get the results for me.

                            Winning Lotto numbers are in numerical order, with numbers in their respective columns. If I want to search the 2nd number column for the number/value contained in the next cell directly below a given number/value, say 10, is there a formula I can type in that will lookup all the values of 10 but only show me the next cell below each value of 10?

                            Column A is the date of the drawing.

                            Column B is the first number in numerical order.

                            Column C is the 2nd number in numerical order. 10 is in Column C, with Row numbers of:

                            24, 53, 61, 76, 78, 106, 111, 134, 143, 153, 156, 184, 191, 202, 214, 226, 248, 284, 338, 345, 401, 410, 413, 425, 438, 448, 455, 508, 515, 542, 552.

                             

                            But I'm wanting results to show what is in the next cell below each #10, so I want a formula to show me Rows:

                            25, 54, 62, 77, 79, 107, 112, 135, 144, 154, 157, 185, 192, 203, 215, 227, 249, 285, 339, 346, 402, 411, 414, 426, 439, 449, 456, 509, 516, 543, 553.

                             

                            Is there a way to get Excel to show me this information?

                              SergeM's avatar - slow icon.png
                              Economy class
                              Belgium
                              Member #123700
                              February 27, 2012
                              4035 Posts
                              Offline
                              Posted: June 23, 2012, 5:47 am - IP Logged

                              Gee, I'm sorry, but none of these suggestions sounds like the info I'm looking for. I'm not interested in pick 3, skips, hits, avg, med, max, due, odd/even, or frequency. Let me rephrase what I'm wanting, hope that Excel can perform it, and someone knows how to get the results for me.

                              Winning Lotto numbers are in numerical order, with numbers in their respective columns. If I want to search the 2nd number column for the number/value contained in the next cell directly below a given number/value, say 10, is there a formula I can type in that will lookup all the values of 10 but only show me the next cell below each value of 10?

                              Column A is the date of the drawing.

                              Column B is the first number in numerical order.

                              Column C is the 2nd number in numerical order. 10 is in Column C, with Row numbers of:

                              24, 53, 61, 76, 78, 106, 111, 134, 143, 153, 156, 184, 191, 202, 214, 226, 248, 284, 338, 345, 401, 410, 413, 425, 438, 448, 455, 508, 515, 542, 552.

                               

                              But I'm wanting results to show what is in the next cell below each #10, so I want a formula to show me Rows:

                              25, 54, 62, 77, 79, 107, 112, 135, 144, 154, 157, 185, 192, 203, 215, 227, 249, 285, 339, 346, 402, 411, 414, 426, 439, 449, 456, 509, 516, 543, 553.

                               

                              Is there a way to get Excel to show me this information?

                              Put your function next to the outer right column, like told above.
                              If you want a list, you need to add a column for concatenation.
                              Maybe there is a complicated array function for this, I don't go that far because I use the VBE.

                              So, yes, it is possible. Naughty