Welcome Guest
You last visited January 18, 2017, 9:04 am
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
ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
5962 Posts
Online
 Posted: July 1, 2009, 1:57 pm - IP Logged

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

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

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

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

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

 Page 2 of 2