Help with Gap detection formula.Previous TopicNext Topic

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.