Welcome Guest
Log In | Register )
You last visited October 23, 2017, 6:44 pm
All times shown are
Eastern Time (GMT-5:00)

Help with Gap detection formula.

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

Page 2 of 2
PrintE-mailLink
CARBOB's avatar - ga lottery.png.gif
COLUMBUS,GA.
United States
Member #4924
June 3, 2004
6145 Posts
Offline
Posted: July 1, 2009, 1:57 pm - IP Logged

Why do you want to use draw # and not skips?

    KnuckleHead's avatar - box
    Thread Starter

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

    I appreaicate the assistance and suggestions from both of you.

    I think that my explanation of what I'm trying to find is wrong.

    In the creation of this formula, I don't want to find the gaps/skips, I want to discover the "draw#" assoiated with the next appearance of the"drawn number" from a specifed range. By having the "draw #", you could go back into the histories and see what numbers were drawn after that draw, which may help picking numbers in future draws.

    I've looked at Carbob's formula, but I don't see that it can "search". That's why I still think that a combination of IF - MATCH - HLOOKUP could work, I just don't comprehend how to combine them together. I'm learning how to combine functions, but I'm just not there yet... I've tried so many different combinations, I've gotten confused about which ones I've tried and didn't try.

    Carbob, if you can, forward the example file to Raven62, maybe with the file, that might help...

    Thank you both for the interest and assistance.

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

      KnuckleHead's avatar - box
      Thread Starter

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

      Why do you want to use draw # and not skips?

      I'm looking at an algorithm that needs the gap/skip figures to calculate properly. But as I go through the histories, it'd be easier to find those numbers with the "draw #" assoiated with the gap/skip number.

      Look at the "pattern map" section in the example file, can you see patterns? I can, but believe it would be easier to find the possible next drawn numbers if the gap/skip was associated with the "draw #".

      As I've said before, maybe I'm going about this all wrong...

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

        KnuckleHead's avatar - box
        Thread Starter

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

        Afternoon all,

        Well, I figured out this much so far:

        @INDEX($MEGA MILLIONS:$D$20..$MEGA MILLIONS:$J$444,0,@MATCH(I8,$MEGA MILLIONS:$E$20..$MEGA MILLIONS:$I$444,0))

        The problem is that the formula only searches for the first "match" then gives the "draw #" as "328" which is correct for the match in the 1st draw column. But, the formula doesn't search the entire range for the next lower "draw #", only the first "match". It also searches on a "column by column" basis instead of a "row by row" basis. The correct answer should have been "407". The "current draw #" is "420".

        Does anyone know how to have the formula look at the previous "draw #", then have the formula search for the next lower "draw #" thorughout the entire range and not just the first column?

        Thank you for any interest and assistance. (I'll be bald soon)

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

          KnuckleHead's avatar - box
          Thread Starter

          United States
          Member #73037
          April 3, 2009
          147 Posts
          Offline
          Posted: July 14, 2009, 6:50 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,"")

           

           

           

          Morning all,

          For anyone that has been watching this post, and is interested, below are the working formulas. If anyone decides to use them, feel free. I had to range my "histories table" so that the range included the labels as seen in the formulas. The sheet displayed above is a separate sheet than the "histories" sheet. To convert from Lotus123 to Excel, change the "@" symbol to "=" and it should work.

           

          I placed this formula in row #13

          @DMAX($TABLE,"DRAW",DRAW<@IF(Y10=0,$D$6,Y10)#AND#(FIRST=Y8#OR#SECOND=Y8#OR#THIRD=Y8#OR#FOURTH=Y8#OR#FIFTH=Y8))

          "Table" is the range name, "Draw" is the label above the draw number, and the "First, Second, Thrid", etc. are the labels above the 1st, 2nd, 3rd, etc. drawn ball number.

           

          I placed this formula in row #14

          @IF(Y10=$D$6,+Y10-Y13,$D$6-Y13)

          I placed this formula in row #16

          @DMAX($TABLE,"DRAW",DRAW<Y13#AND#(FIRST=Y8#OR#SECOND=Y8#OR#THIRD=Y8#OR#FOURTH=Y8#OR#FIFTH=Y8))

          I placed this formula in row #17

          +Y13-Y16

           

          I realize that most of you already know how to do something like this, but I posted it for those that don't have any idea, as I didn't. Maybe it will save someone else time in the future.

          Thanks to everyone that assisted.

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