http://www.lotterypost.com/thread/196121 Lottery Post Forum Topic: Help with Gap detection formula. en-us Lottery Post RSS Generator http://www.lotterypost.com/thread/196121/1350772 http://www.lotterypost.com/thread/196121/1350772 Tue, 14 Jul 2009 10:50:03 GMT KnuckleHead
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 ]]]> KnuckleHead http://www.lotterypost.com/thread/196121/1343227 http://www.lotterypost.com/thread/196121/1343227 Sat, 04 Jul 2009 17:38:18 GMT KnuckleHead
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 ]]]> KnuckleHead http://www.lotterypost.com/thread/196121/1340936 http://www.lotterypost.com/thread/196121/1340936 Wed, 01 Jul 2009 19:38:18 GMT KnuckleHead
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...]]> KnuckleHead http://www.lotterypost.com/thread/196121/1340916 http://www.lotterypost.com/thread/196121/1340916 Wed, 01 Jul 2009 19:22:29 GMT KnuckleHead
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 ]]]> KnuckleHead http://www.lotterypost.com/thread/196121/1340851 http://www.lotterypost.com/thread/196121/1340851 Wed, 01 Jul 2009 17:57:28 GMT CARBOB CARBOB http://www.lotterypost.com/thread/196121/1340841 http://www.lotterypost.com/thread/196121/1340841 Wed, 01 Jul 2009 17:43:53 GMT Raven62
@COUNTIF($DIGIT REPEATS:$AZ$28..$DIGIT REPEATS:$AZ$431,A96]]> Raven62 http://www.lotterypost.com/thread/196121/1340810 http://www.lotterypost.com/thread/196121/1340810 Wed, 01 Jul 2009 17:08:13 GMT KnuckleHead KnuckleHead http://www.lotterypost.com/thread/196121/1340800 http://www.lotterypost.com/thread/196121/1340800 Wed, 01 Jul 2009 16:58:08 GMT CARBOB CARBOB http://www.lotterypost.com/thread/196121/1340776 http://www.lotterypost.com/thread/196121/1340776 Wed, 01 Jul 2009 16:05:28 GMT CARBOB
COUNTIF('DIGIT REPEATS'!$AZ$28:$AZ$431,A96]]> CARBOB http://www.lotterypost.com/thread/196121/1340767 http://www.lotterypost.com/thread/196121/1340767 Wed, 01 Jul 2009 16:00:16 GMT KnuckleHead
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]]> KnuckleHead http://www.lotterypost.com/thread/196121/1340720 http://www.lotterypost.com/thread/196121/1340720 Wed, 01 Jul 2009 15:02:13 GMT CARBOB CARBOB http://www.lotterypost.com/thread/196121/1339889 http://www.lotterypost.com/thread/196121/1339889 Tue, 30 Jun 2009 18:16:36 GMT KnuckleHead KnuckleHead http://www.lotterypost.com/thread/196121/1339750 http://www.lotterypost.com/thread/196121/1339750 Tue, 30 Jun 2009 15:50:54 GMT CARBOB CARBOB http://www.lotterypost.com/thread/196121/1339730 http://www.lotterypost.com/thread/196121/1339730 Tue, 30 Jun 2009 15:31:00 GMT KnuckleHead
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]]> KnuckleHead http://www.lotterypost.com/thread/196121/1338278 http://www.lotterypost.com/thread/196121/1338278 Sun, 28 Jun 2009 10:06:52 GMT CARBOB CARBOB http://www.lotterypost.com/thread/196121/1338276 http://www.lotterypost.com/thread/196121/1338276 Sun, 28 Jun 2009 09:47:41 GMT mybolade
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.]]> mybolade http://www.lotterypost.com/thread/196121/1337873 http://www.lotterypost.com/thread/196121/1337873 Sat, 27 Jun 2009 18:47:34 GMT CARBOB
=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 http://www.lotterypost.com/thread/196121/1337862 http://www.lotterypost.com/thread/196121/1337862 Sat, 27 Jun 2009 18:25:01 GMT KnuckleHead
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 ]]]> KnuckleHead http://www.lotterypost.com/thread/196121/1337837 http://www.lotterypost.com/thread/196121/1337837 Sat, 27 Jun 2009 17:54:54 GMT mybolade
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 ]]]> mybolade http://www.lotterypost.com/thread/196121 http://www.lotterypost.com/thread/196121 Sat, 27 Jun 2009 17:30:24 GMT KnuckleHead
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 ]]]> KnuckleHead