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)

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

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.

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...

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)

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.