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

Help with Gap detection formula.

Topic closed. 19 replies. Last post 7 years ago by KnuckleHead.

Page 1 of 2
PrintE-mailLink
KnuckleHead's avatar - box

United States
Member #73037
April 3, 2009
147 Posts
Offline
Posted: June 27, 2009, 1:30 pm - IP Logged

Morning all,

I hope some one can help with this idea. I've set up a table to discover "gaps" in drawn numbers. I'm trying to figure out how to "match" a specific number (ie. 1, 2, 3, 4, etc.) , in the drawn history range. The answer would be the "Drawing #". Then in the cell next to it, have the "gap" number displayed for how many draws have gone by since the last time that number was drawn. I figure that I need to use some kind of a combination of "MATCH" and "HLOOKUP", but I haven't had any luck in combining them to work properly.

 

As an example, I have my Mega Millions histories set up like this:

     B                    C         D      E       F      G      H      I

Draw Date     Draw #   1st   2nd   3rd   4th   5th   6th

06/23/2009      418       12    31     16     50   14     9

06/19/2009      417        4      9      46     16   12    44

                                  (etc. down)

 

What I believe I need to do is to verify the "draw#" that a specific number appeared, then search for the next appearance of that specific number and get the "draw #" from the row it appears in. In the next cell, it should be a simple "plus/minus" formula based on the "draw #'s" to display the "gap". My problem is "how" to locate the next drawn matching number and get the "draw #" that it's in.

Am I thinking correctly for this idea? and does anyone know how to create a formula to do this? I'm open to suggestions since I'm running out of hair...

Thanks ahead of time for any suggestions.

          The only DUMB question is the one question you DID NOT ask...

    mybolade's avatar - praying hands.jpg
    Detroit, MI
    United States
    Member #8705
    November 15, 2004
    1396 Posts
    Offline
    Posted: June 27, 2009, 1:54 pm - IP Logged

    Knucklehead,

    You  need to list all the pairs or numbers you want to match in a table. Then in another part of excel (perferably on the same sheet set up your draws. Make sure you set up the draws so each number has a cell. 
    What I use is countif, and have a column for the today's date and the date you start from. Then I list a range. Therefore, countif will count how many times that number has fallen in that range. I hope this helps. I am working with pairs. Below is a sample, the formula would be in the cell

    where you see 51, 46 etc. The formula is: =countif (range),(cell where the 51 is)

    Note there should be a space between the dates.

    005131-Dec27-Jun
    114629-Dec27-Jun
    223413-Dec27-Jun
    333827-Dec27-Jun
    444928-Dec27-Jun
    553224-Dec27-Jun
    665630-Dec27-Jun
    774216-Dec27-Jun
    884522-Dec27-Jun
    995930-Dec27-Jun
      KnuckleHead's avatar - box

      United States
      Member #73037
      April 3, 2009
      147 Posts
      Offline
      Posted: June 27, 2009, 2:25 pm - IP Logged

      Afternoon mybolade

      I have "frequency" tables setup for 11 draws, 27 draws, 59 draws, and Total draws. They all use the "COUNTIF" formula. But, I don't have them setup to show the difference in drawn dates or draw #'s, just how many times they appear in a specific table.

      In the different tables I listed above, a specific number could appear several times. This is why I decided to "find" each number and the "draw#" each time the number appears. This idea may be off base, but I'm thinking that having the number of times between each numbers appearance could be useful. I haven't yet progressed to locating pairs or triples, I have however, created a "pattern map" that when I take the time to look at, it displays some very interesting patterns in the drawn history.

      Thank you though, you have a different layout, one that I hadn't considered before.

                The only DUMB question is the one question you DID NOT ask...

        CARBOB's avatar - FL LOTTERY_LOGO.png
        ORLANDO, FLORIDA
        United States
        Member #4924
        June 3, 2004
        5914 Posts
        Offline
        Posted: June 27, 2009, 2:47 pm - IP Logged

        Afternoon mybolade

        I have "frequency" tables setup for 11 draws, 27 draws, 59 draws, and Total draws. They all use the "COUNTIF" formula. But, I don't have them setup to show the difference in drawn dates or draw #'s, just how many times they appear in a specific table.

        In the different tables I listed above, a specific number could appear several times. This is why I decided to "find" each number and the "draw#" each time the number appears. This idea may be off base, but I'm thinking that having the number of times between each numbers appearance could be useful. I haven't yet progressed to locating pairs or triples, I have however, created a "pattern map" that when I take the time to look at, it displays some very interesting patterns in the drawn history.

        Thank you though, you have a different layout, one that I hadn't considered before.

        Are you wanting to find , how long it has been since the number was drawn? This formula will count the skips.

        =IF(COLUMNS(AP19:AP19)<=COUNTIF($Z$17:$Z$7668,$AJ19),SMALL(IF($Z$17:$Z$7668=$AJ19,ROW($Z$17:$Z$7668)-ROW($Z$17)+1),COLUMNS(AP19:AP19))-1,"")

        You will have to change the ranges. Here's an image.

          mybolade's avatar - praying hands.jpg
          Detroit, MI
          United States
          Member #8705
          November 15, 2004
          1396 Posts
          Offline
          Posted: June 28, 2009, 5:47 am - IP Logged

          Carbob,

          Do you need to change the range each time to detect when is the last time it fell. In otherwords if it falls today would I start the range from that date? Do I just put this formula in for every number that falls. So each day I know how long it has been since it fell.

          I plan to use this formula with my stats, I think it will save me work.

          Thanks for sharing.

          Bolade'

          "Excellence is the result of
           Caring more than others think is wise;
           Risking more than others think is safe,
           Dreaming more than others think is practical, and
           Expecting more than others think is possible."  Author Unknown

            CARBOB's avatar - FL LOTTERY_LOGO.png
            ORLANDO, FLORIDA
            United States
            Member #4924
            June 3, 2004
            5914 Posts
            Offline
            Posted: June 28, 2009, 6:06 am - IP Logged

            Yes, you have to update the range after every draw.

              KnuckleHead's avatar - box

              United States
              Member #73037
              April 3, 2009
              147 Posts
              Offline
              Posted: June 30, 2009, 11:31 am - IP Logged

              Morning CARBOB

              RE: The formula posted.

              I'm trying to reference a "draw #", then look in the history field below that "draw #" for the next appearence of the "drawn" number. Then count (subtract) the difference between the "draw #'s". I don't think I'm making sense describing it though.

              Would you have a better understanding if I attached an example file in an e-mail to you so you could better understand/see what I'm trying to do? If this is agreeable, send me your e-mail address in a PM and I'll forward the questionable file.

              OPPS...Before we go any further, can your version of Excel open a Lotus.123 file?

                        The only DUMB question is the one question you DID NOT ask...

                CARBOB's avatar - FL LOTTERY_LOGO.png
                ORLANDO, FLORIDA
                United States
                Member #4924
                June 3, 2004
                5914 Posts
                Offline
                Posted: June 30, 2009, 11:50 am - IP Logged

                Morning CARBOB

                RE: The formula posted.

                I'm trying to reference a "draw #", then look in the history field below that "draw #" for the next appearence of the "drawn" number. Then count (subtract) the difference between the "draw #'s". I don't think I'm making sense describing it though.

                Would you have a better understanding if I attached an example file in an e-mail to you so you could better understand/see what I'm trying to do? If this is agreeable, send me your e-mail address in a PM and I'll forward the questionable file.

                OPPS...Before we go any further, can your version of Excel open a Lotus.123 file?

                The formula I posted counts the number of draws since a digit hit. I also have a formula thatwill use that data and count the skips for each time the digit hit. Sent you a PM.

                  KnuckleHead's avatar - box

                  United States
                  Member #73037
                  April 3, 2009
                  147 Posts
                  Offline
                  Posted: June 30, 2009, 2:16 pm - IP Logged

                  The formula I posted counts the number of draws since a digit hit. I also have a formula thatwill use that data and count the skips for each time the digit hit. Sent you a PM.

                  Thank you, files in the air.

                            The only DUMB question is the one question you DID NOT ask...

                    CARBOB's avatar - FL LOTTERY_LOGO.png
                    ORLANDO, FLORIDA
                    United States
                    Member #4924
                    June 3, 2004
                    5914 Posts
                    Offline
                    Posted: July 1, 2009, 11:02 am - IP Logged

                    Is this somewhat close to what you are looking for? You will have change your worksheet around. The formula is =IF(ISNUMBER(MATCH(C$8,'MEGA MILLIONS'!$E24:$I24,0)),'MEGA MILLIONS'!$D24,"")

                     

                     

                     

                      KnuckleHead's avatar - box

                      United States
                      Member #73037
                      April 3, 2009
                      147 Posts
                      Offline
                      Posted: July 1, 2009, 12:00 pm - IP Logged

                      Hello CARBOB,

                      I'm looking at the formula now, changing it to reference different cells isn't a problem, but, what does the "!" symbol mean in Excel?

                                The only DUMB question is the one question you DID NOT ask...

                        CARBOB's avatar - FL LOTTERY_LOGO.png
                        ORLANDO, FLORIDA
                        United States
                        Member #4924
                        June 3, 2004
                        5914 Posts
                        Offline
                        Posted: July 1, 2009, 12:05 pm - IP Logged

                        Whenever you reference another worksheet in the same workbook, 'DIGIT REPEATS'! its required. You may use something else in 1-2-3.

                         

                        COUNTIF('DIGIT REPEATS'!$AZ$28:$AZ$431,A96)

                          CARBOB's avatar - FL LOTTERY_LOGO.png
                          ORLANDO, FLORIDA
                          United States
                          Member #4924
                          June 3, 2004
                          5914 Posts
                          Offline
                          Posted: July 1, 2009, 12:58 pm - IP Logged

                          Whenever you reference another worksheet in the same workbook, 'DIGIT REPEATS'! its required. You may use something else in 1-2-3.

                           

                          COUNTIF('DIGIT REPEATS'!$AZ$28:$AZ$431,A96)

                          This is the way I track skips or what you refer too, as gaps. The first row is current skip, the following rows are the number of skips before the next hit.

                           

                           

                            KnuckleHead's avatar - box

                            United States
                            Member #73037
                            April 3, 2009
                            147 Posts
                            Offline
                            Posted: July 1, 2009, 1:08 pm - IP Logged

                            Whenever you reference another worksheet in the same workbook, 'DIGIT REPEATS'! its required. You may use something else in 1-2-3.

                             

                            COUNTIF('DIGIT REPEATS'!$AZ$28:$AZ$431,A96)

                            I'm looking in the Lotus help file, but it doesn't appear to have a conversion for Excel's use of "!" in this way. I'm still looking though...

                                      The only DUMB question is the one question you DID NOT ask...

                              Raven62's avatar - binary
                              New Jersey
                              United States
                              Member #17843
                              June 28, 2005
                              49835 Posts
                              Offline
                              Posted: July 1, 2009, 1:43 pm - IP Logged

                              I'm looking in the Lotus help file, but it doesn't appear to have a conversion for Excel's use of "!" in this way. I'm still looking though...

                              Maybe this will help:

                              @COUNTIF($DIGIT REPEATS:$AZ$28..$DIGIT REPEATS:$AZ$431,A96)

                              A mind once stretched by a new idea never returns to its original dimensions!