Welcome Guest
You last visited July 23, 2017, 6:26 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 1 of 2

United States
Member #73037
April 3, 2009
147 Posts
Offline
 Posted: June 27, 2009, 1:30 pm - IP Logged

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.

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

Detroit, MI
United States
Member #8705
November 15, 2004
1396 Posts
Offline
 Posted: June 27, 2009, 1:54 pm - IP Logged

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.

 00 51 31-Dec 27-Jun 11 46 29-Dec 27-Jun 22 34 13-Dec 27-Jun 33 38 27-Dec 27-Jun 44 49 28-Dec 27-Jun 55 32 24-Dec 27-Jun 66 56 30-Dec 27-Jun 77 42 16-Dec 27-Jun 88 45 22-Dec 27-Jun 99 59 30-Dec 27-Jun

United States
Member #73037
April 3, 2009
147 Posts
Offline
 Posted: June 27, 2009, 2:25 pm - IP Logged

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.

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

ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
6103 Posts
Offline
 Posted: June 27, 2009, 2:47 pm - IP Logged

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.

Detroit, MI
United States
Member #8705
November 15, 2004
1396 Posts
Offline
 Posted: June 28, 2009, 5:47 am - IP Logged

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.

"Excellence is the result of
Caring more than others think is wise;
Risking more than others think is safe,
Dreaming more than others think is practical, and
Expecting more than others think is possible."  Author Unknown

ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
6103 Posts
Offline
 Posted: June 28, 2009, 6:06 am - IP Logged

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

United States
Member #73037
April 3, 2009
147 Posts
Offline
 Posted: June 30, 2009, 11:31 am - IP Logged

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 only DUMB question is the one question you DID NOT ask...

ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
6103 Posts
Offline
 Posted: June 30, 2009, 11:50 am - IP Logged

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.

United States
Member #73037
April 3, 2009
147 Posts
Offline
 Posted: June 30, 2009, 2:16 pm - IP Logged

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.

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

ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
6103 Posts
Offline
 Posted: July 1, 2009, 11:02 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,"")

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

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?

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

ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
6103 Posts
Offline
 Posted: July 1, 2009, 12:05 pm - IP Logged

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)

ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
6103 Posts
Offline
 Posted: July 1, 2009, 12:58 pm - IP Logged

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.

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

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)

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

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

New Jersey
United States
Member #17843
June 28, 2005
63584 Posts
Offline
 Posted: July 1, 2009, 1:43 pm - IP Logged

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)

A mind once stretched by a new idea never returns to its original dimensions!

 Page 1 of 2