Help with Gap detection formula.

Reply #19

KnuckleHead — Tue, 14 Jul 2009 10:50:03 GMT

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#(FIR... [ More ]

Reply #18

KnuckleHead — Sat, 04 Jul 2009 17:38:18 GMT

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. T... [ More ]

Reply #17

KnuckleHead — Wed, 01 Jul 2009 19:38:18 GMT

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

Reply #16

KnuckleHead — Wed, 01 Jul 2009 19:22:29 GMT

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 formu... [ More ]

Reply #15

CARBOB — Wed, 01 Jul 2009 17:57:28 GMT

Why do you want to use draw # and not skips

Reply #14

Raven62 — Wed, 01 Jul 2009 17:43:53 GMT

Maybe this will help:

@COUNTIF($DIGIT REPEATS:$AZ$28..$DIGIT REPEATS:$AZ$431,A96

Reply #13

KnuckleHead — Wed, 01 Jul 2009 17:08:13 GMT

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

Reply #12

CARBOB — Wed, 01 Jul 2009 16:58:08 GMT

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.

Reply #11

CARBOB — Wed, 01 Jul 2009 16:05:28 GMT

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

Reply #10

KnuckleHead — Wed, 01 Jul 2009 16:00:16 GMT

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

Reply #9

CARBOB — Wed, 01 Jul 2009 15:02:13 GMT

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,

Reply #8

KnuckleHead — Tue, 30 Jun 2009 18:16:36 GMT

Thank you, files in the air.

Reply #7

CARBOB — Tue, 30 Jun 2009 15:50:54 GMT

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.

Reply #6

KnuckleHead — Tue, 30 Jun 2009 15:31:00 GMT

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... [ More ]

Reply #5

CARBOB — Sun, 28 Jun 2009 10:06:52 GMT

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

Reply #4

mybolade — Sun, 28 Jun 2009 09:47:41 GMT

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.

Reply #3

CARBOB — Sat, 27 Jun 2009 18:47:34 GMT

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.

Reply #2

KnuckleHead — Sat, 27 Jun 2009 18:25:01 GMT

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 havi... [ More ]

Reply #1

mybolade — Sat, 27 Jun 2009 17:54:54 GMT

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 wo... [ More ]

Help with Gap detection formula.

KnuckleHead — Sat, 27 Jun 2009 17:30:24 GMT

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 c... [ More ]