Help with Gap detection formula.Previous TopicNext Topic

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.

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.

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.

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.

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

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.

Thank you, files in the air.

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,"")

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?

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.

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)