help with excel formula to count skips

[hypersoniq]

I have all of the data for the PA daily number (pick 3) in an excel spreadsheet... what I would like to do is find a formula or macro to count skips per number by position.
I have it laid out as such...
Column = Contents
A = Draw Number (sequential from 1 to 9,169)
B = Date of Drawing
C = the drawn combo (e.g. 251)
D = FIRST number drawn (2 from C)
E = SECOND number drawn (5 from C)
F = THIRD numberdrawn (1 from C)

What I would like to do is have the following skip table result...
pos 1 pos 2 pos 3
days days days
Number out out out
1 46 12 3
2 2 13 1
3 38 14 6
4 4 3 12
5 5 24 26
6 0 1 7
7 20 0 10
8 1 4 8
9 34 10 0
0 27 6 13

From this, I will take the top 3 by position, from the example above...

1 5 5 <longest out
3 3 0 <2nd longest out
9 2 4 <3rd longest out

As a formula or macro, I need something flexible enough to alter the ranges so I can perform back testing of this method of picking numbers (test for straight and box hits)... I already have formulas to test for straights and boxes, I need the formula (or at least an idea of how to go about solving this problem) to avoid the need to cover over 9,000 draws by hand.

Thanks in advance for any help ;-)

 

[hypersoniq]

that result table got a bit mangled...

pos 1 pos 2 pos 3
days days days
Number out out out
1 46 12 3
2 2 13 1
3 38 14 6
4 4 3 12
5 5 24 26
6 0 1 7
7 20 0 10
8 1 4 8
9 34 10 0
0 27 6 13

I don't know how to find the occurrence of a number by going backwards in the list...
by the example above, the 0's indicate the most recent drawn, as those numbers would be 0 days out... tonight's PA daily number was 6-7-9.

 

[hypersoniq]

oh well, I guess the table will remain mangled :-(

it looks good in excel tho ;-)

 

[GillesD]

Skips for Pick 3 draws

Let’s say your data in Excel has the draw number in column A and N1, N2 and N3 in columns B, C and D starting on row #3 and going down. Row 2 can be used for labels. Note it might be best if the width of columns is small in order to see more (a width of 5 is enough).

A - Skips not considering the position

The method listed here does take into consideration the position a number has come out but simply when it did come out last.

In cells F1 to O1, enter the consecutive numbers, 0,1, 2, …, 9. In cell F3, enter the formula =IF(COUNTIF($B3:$D3,F$1)>=1,0,F2+1). Then copy this formula across to column O, and down for all the draws.

This will give you a 0 if the number in the top row has come out in the draw or otherwise a number indicating how many draws the number has not come out.

B – Skips considering the position

This method now takes into consideration the position of the number, either in first, second or third position.

In cells F1 to O1, enter the consecutive numbers, 0,1, 2, …, 9 for numbers in the first position, then repeat this, in cells Q1 to Z1 for numbers in the second position, and in cells AB1 to AK1 for numbers in the third position.
In cell F3, enter the formula =IF($B3=F$1,0,F2+1), in cell Q3, the formula =IF($C3=Q$1,0,Q2+1) and in cell AB3, the formula =IF($D3=AB$1,0,AB2+1). Then copy each formula across each set of data and down for all draws.

This will give you a 0 if the number in the top row has come out in the draw or otherwise a number indicating how many draws the number has not come out in a specific position depending on which block of data you are looking at.

 

[hypersoniq]

wow, thanks GillesD!

 

[hypersoniq]

It works for the most out!
I have drawing number results in columns D to F.

After setting (by position) the next 30 columns, I can extract the longest out digit (each position) with

=LOOKUP(MAX(J2:S2),J2:S2,J$1:S$1) in cell AN2
=LOOKUP(MAX(T2:AC2),T2:AC2,T$1:AC$1) in cell AO2, and
=LOOKUP(MAX(AD2:AM2),AD2:AM2,AD$1:AM$1) in cell AP2

then, in cell AQ2, recreate the combo with

=(AN2*100)+(AO2*10)+AP2

and finally check for a straight or boxed hit with

=IF(AQ2=(($D2*100)+($E2*10)+$F2),"S",IF(AQ2=(($D2*100)+($F2*10)+$E2),"B",IF(AQ2=(($F2*100)+($E2*10)+$D2),"B",IF(AQ2=(($F2*100)+($D2*10)+$E2),"B",IF(AQ2=(($E2*100)+($D2*10)+$F2),"B",IF(AQ2=(($E2*100)+($F2*10)+$D2),"B"," "))))))

in cell AR2.

At the bottom, it's simply a matter of countif to get straights and boxes

Straights
=COUNTIF(AR1:AR9174,"=S")

and boxes (which includes the straight hits as box hits also)
=COUNTIF(AR1:AR9174,"=B")+AR9176


This is great for the most out because it works... now to figure out how to count the next 2 longest outs... I'll save that for another day.

Results... MOST OUT has produced a grand total of 4 STRAIGHT hits and 18 BOXED (including the 4 straights) in 9,173 drawings... not exactly the next big thing, but it was nice to be able to backtest it... thanks again!

 

[hypersoniq]

for some reason, the lookup is not returning the correct number for the first 2 columns... results will undoubtedly be affected... still trying to figure it out, because it works for the third column...