Skips Between Game

[emilyng]

Hi all, I am trying to calculate for my lotto number with number of skips for each drawing,

example like below

D1 D2 D3 D4 D5 D6
3 5 10 23 48 52
4 8 11 48 52 55
8 9 10 25 34 40 54

example:

number 8 hit and hit again on next drawing, the skip is 0, number 10 hit again at the first draw, then hit again on 3rd draw, the skip is 2

Is there any macro or formula i could use?

thanks

 

[PAB]

Hi emilyng,

I am confused .
Is this not the same request as asked in the thread? ...

Lotto Forums > Lotto Forums General Discussion > Questions & Answers > Excel Help

Hi PAB

Thanks for the advice. I am able to figure out at last. Anyway nice meeting you.

Anyway, if you could give me the cells where your drawn data is I will be able to provide you with the SKIPS formulas for calculating the number of draws since each of the numbers were last drawn.
I also need to know what sort of Lotto it is, for example, 649 or such like.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[emilyng]

Hi PAB
Sorry. It is not the same problem. Anyway here is the details:

Cell D to I is the draw number (D1, D2,...D6)

Here is draw example:

D1 D2 D3 D4 D5 D6
3 5 8 23 34 52
4 15 8 23 35 55
9 15 34 42 44 50


End result example
D1 D2 D3 D4 D5 D6
?? ?? 00 00 ?? ??
?? 00 01 ?? ?? ??

I am playing 6/58 game

Thanks

 

[PAB]

Hi emilyng,

Try this.

Cell D to I is the draw number (D1, D2,...D6)

Did you mean cells D1, E1, F1, G1, H1 & I1 are the first set of 6 drawn numbers? If so ...

ADD two rows at the top of your SpreadSheet so your data starts in cells D3, E3, F3, G3, H3 & I3.
In cells K1:BP1 enter the numbers 1 to 58.
Enter the following formula in cell K3 ...

=IF(COUNTIF($D3:$I3,K$1)>=1,0,K2+1)

Copy the above formula along to cell BP3 and then down.
This will give you the number of draws since each number from 1 to 58 has been drawn.
Let me know how you get on!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[emilyng]

Hi PAB,

Thanks a lot.

I tried already. it is somthing similar to what I am looking for, except, the skips, must be positional dependend

for example

Skip
D1 D2 D3 D4 D5 D6
?? ?? 00 00 ?? ??
?? 00 01 ?? ?? ??

 

[PAB]

Hi emilyng,

Try this FORMULA route.
I have used formulas for this and it involves many columns of data but it very simple to set up.
ADD two rows at the top of your SpreadSheet so your data starts in cells D3, E3, F3, G3, H3 & I3.

In cells K1 > BP1 enter the numbers 1 to 58.
In cells BR1 > DW1 enter the numbers 1 to 58.
In cells DY1 > GD1 enter the numbers 1 to 58.
In cells GF1 > IK1 enter the numbers 1 to 58.
In cells IM1 > KR1 enter the numbers 1 to 58.
In cells KT1 > MY1 enter the numbers 1 to 58.

Position 1
Enter the following formula in cell K3 =IF($D3=K$1,0,K2+1)
Copy the above formula along to cell BP3 and then down.

Position 2
Enter the following formula in cell BR3 =IF($E3=BR$1,0,BR2+1)
Copy the above formula along to cell DW3 and then down.

Position 3
Enter the following formula in cell DY3 =IF($F3=DY$1,0,DY2+1)
Copy the above formula along to cell GD3 and then down.

Position 4
Enter the following formula in cell GF3 =IF($G3=GF$1,0,GF2+1)
Copy the above formula along to cell IK3 and then down.

Position 5
Enter the following formula in cell IM3 =IF($H3=IM$1,0,IM2+1)
Copy the above formula along to cell KR3 and then down.

Position 6
Enter the following formula in cell KT3 =IF($I3=KT$1,0,KT2+1)
Copy the above formula along to cell MY3 and then down.

This will give you the number of draws since each number from 1 to 58 has been drawn in EACH position.
Let me know how you get on!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[PAB]

Hi emilyng,

I have just been thinking about this and how to make it more manageable.
Once you have done the above do this.

(1) Insert 8 columns starting from K, this will then give the SKIPS data starting in column S.
(2) In cells NI3:NI60 enter the numbers 19 to 76.
(3) In cells NJ3:NJ60 enter the numbers 78 to 135.
(4) In cells NK3:NK60 enter the numbers 137 to 194.
(5) In cells NL3:NL60 enter the numbers 196 to 253.
(6) In cells NM3:NM60 enter the numbers 255 to 312.
(7) In cells NN3:NN60 enter the numbers 314 to 371.
(8) In cell A1 enter the formula =MAX(A2:A1000). CHANGE THIS FORMULA AS NEEDED TO THE COLUMN LETTER WITH YOUR DRAW NUMBERS IN SO THE LOOKUPS WILL KNOW WHAT THE LAST DRAW NUMBER IS AND GIVE THE CORRECT SKIPS DATA.
(9) In cells K3:K60 enter the numbers 1 To 58.
(10) In cell L3 enter the formula =VLOOKUP($A$1,S$3:BX$102,$K3,NI3) and copy down.
(11) In cell M3 enter the formula =VLOOKUP($A$1,BZ$3:EE$102,$K3,NJ3) and copy down.
(12) In cell N3 enter the formula =VLOOKUP($A$1,EG$3:GL$102,$K3,NK3) and copy down.
(13) In cell O3 enter the formula =VLOOKUP($A$1,GN$3:IS$102,$K3,NL3) and copy down.
(14) In cell P3 enter the formula =VLOOKUP($A$1,IU$3:KZ$102,$K3,NM3) and copy down.
(15) In cell Q3 enter the formula =VLOOKUP($A$1,LB$3:NG$102,$K3,NN3) and copy down.
(16) HIDE columns S:NN.
Now you have got the SKIPS for EACH position for EACH number in columns K:Q.
The last two posts are just a quick and easy formula solution to achieve what you are after.
Let me know how you get on!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[emilyng]

Hi PAB

Thanks for willing to spend some time on my problem. This is close, but the formula did not consider the same number drew on the other position, for example, for unit #8, It draw on draw #2 and draw #2, so the end result will be:

Draw #D1 D2 D3 D4 D5 D6 Sk1 SK2 Sk3 SK4 SK5 SK6
1 3 5 10 23 48 52 NN NN NN NN NN NN
2 4 8 11 48 52 55 NN NN NN 00 00 NN
3 8 9 10 25 34 40 54 00 NN 01 NN NN NN NN

In draw #2, number 48, is repeated, then no skip at SK4, skip (SK = skip ) is equal to 00, and number 52 at sk5 (skip 5, 5 = position), then it is also 00

regardless of what position of nbr 48, we count the skip in between the game

Thanks a lot and have a nice day

 

[PAB]

Hi emilyng,

The first example I gave you as requested in Posts 1 & 3 of this thread calculates the number of SKIPS for EACH number from 1 to 58 as of the last draw, regardless of which position they were drawn in previously.
It also by the way, gives you a viewable SKIPS history for EACH number.

The second example I gave you as requested in Post 5 of this thread calculates the number of SKIPS for EACH number from 1 to 58 for EACH position with regard to EACH position they were drawn in previously.
Basically, it gives you the SKIPS for EACH number for EACH position.
Again, it also gives you a viewable SKIPS history for EACH number for EACH position.

Are you saying that there is a THIRD alternative?

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[Frank]

Hi,

I wonder if I may be allowed to make an observation here ? I'm not going to get into spreadsheet development here as PAB has covered this very well. I was intrigued by emilyng's suggestion that ( in Post 5) that the skips were required taking position into account. Presumably by this, it was meant the position the ball appears in after the result has been sorted into ascending order, and measuring the skips of each ball individually in strict sorted positional columns ? It think this second measure of skips , which PAB spent some time developing is what was being suggested. Emilyng's latest answer is puzzling as it seems to contradict this interpretation.

I wonder whether Emilyng was aware of the statistical differences between the theoretical skip values of numbers after they have been sorted into ascending order ? Not all balls are equal in all positions.
I apologise if this has been discussed elsewhere, but it won't do any harm to just mention it.

By way of example, (6/58)consider the result 30,1,58,45,10,12. As they stand skip evaluation by position is meaningful. However as soon as you sort them :-
1,10,12,30,45,58
What is the probability of number 1 appearing in any other position other than the first ? The answer is nil, so the theoretical skip value for number 1 in the other columns is infinite. The same applies to number 58 in the last column, It can only ever be there, nowhere else, for any possible result. The other numbers have variable finite probabilites (and therefore skip values) because they can be juxtaposed and positioned in any column depending on the relative value of other balls in the result.

In fact for a 6/58 Lottery these are the theoretical probability statistics for those balls appearing in these positions:- (A,B,C etc are column positions after sorting)
Edit..(Note I have left out number 12, but you get the idea...)

Number...........A.................B................C...............D...............E...............F

1....................0.103.......... 0,................0,...............0,..............0..............0
10.................0.042.......... 0.043 ..........0.015........0.002....0.00015.........0.0000031
30................0.002.......... 0.015......... 0.033 ........0.034 ......... 0.01643....... 0.0029
45................0.000........ 0.001 ........ 0.007 ......... .. 0.026 ....... 0.044........ 0.027
58............... 0,................0,...............0,...............0...............0..............0.103

These lead to theoretical average skips (divide number of combinations in lottery by the probablity)
Theoretical Skip statistics for 6/58 lottery balls in these sorted positions

Number...........A.................B................C...............D...............E...............F
1.............9.67................N/A................N/A.,........N/A............N/A............N/A.
10...........23.64.................... 23 ......... 65............ 427......... 6,692 ............. 321,233
30....... 411.84........... .... 68........... .... 30........ ...... . 29 ....... 61....... .... .... 341
45....... 31,449.38......... 1,287 ........ ..... 150.......... ... 39 ........... .. 23........ .... 37
58................N/A................N/A.,.............N/A.............N/A.............N/A........... 9.67

So if you do monitor skips by sorted position you need to be aware of this information in order to make an informed decision from the outcome. Perhaps in a different thread I can show how to create a spreadsheet table for most lotteries giving the above statistics.

 

[emilyng]

Hi Frank

Thanks for the reply. I would like to understand more on the statistical difference on the theoritical skip
If would be good if you could help to create a spreadsheet table for the above given statistic

Thanks in advance

 

[PAB]

Frank

Great post and explanation as usual Frank, very informative and thought provoking.
Thanks also for your kind words.
I think that there is some mileage in setting up a SpreadSheet for the statistical differences between the theoretical skip values of numbers after they have been sorted into ascending order and the fact that NOT all balls are equal in all positions as described in your previous post.
I agree that skip evaluation by position in the unsorted state as opposed to the sorted state is meaningful.
I think if you decide to ...

Perhaps in a different thread I can show how to create a spreadsheet table for most lotteries giving the above statistics.

... do this it will be warmly welcomed, especially if both the UNSORTED and SORTED statistics are addressed.

As a side note, I do hate that the formatting gets screwed up in posts and that we cannot post VB or VBA code to show correctly.

emilyng

With regard to Post 9 which you have NOT replied to or given feedback about as yet ...

The first example I gave you as requested in Posts 1 & 3 of this thread calculates the number of SKIPS for EACH number from 1 to 58 as of the last draw, regardless of which position they were drawn in previously.
It also by the way, gives you a viewable SKIPS history for EACH number.

The second example I gave you as requested in Post 5 of this thread calculates the number of SKIPS for EACH number from 1 to 58 for EACH position with regard to EACH position they were drawn in previously.
Basically, it gives you the SKIPS for EACH number for EACH position.
Again, it also gives you a viewable SKIPS history for EACH number for EACH position.

Are you saying that there is a THIRD alternative?

If the THIRD alternative is for SKIPS with regard to the UNSORTED drawn order then my second example STILL applies, all you need to do is to enter the balls as they were drawn into the SpreadSheet, therefore getting the number of SKIPS for EACH number from 1 to 58 for EACH position.

ALWAYS leave feedback. This does NOT only help the person helping you but also helps ALL other members and guests following the thread.
Leaving feedback is not only polite but will give you more of a chance of people helping you in the future.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[emilyng]

Hi PAB,
Sorry for not replying as I think I miss your reply out. In fact I thought you did not reply me after my last reply. Anyway, I do try out your suggestion, just the end result I need I need is like this:
Sn d1 d2 d3 d4 d5 d6
1. 00. Nn

Sn is the serial od the draw and d1 to d6 is the skip wrt their position if draw.
Btw, how can I upload my file here so that you will be easier to visualize?

Thanks in advance for the help and it is nice meeting you

 

[PAB]

Hi emilyng,

In response to ...

BTW, how can I upload my file here so that you will be easier to visualize?

... you can use www.mediafire.com which is a FREE service. You can then post the link to the file here.
Hope this helps.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[Frank]

Thanks PAB and emilyng for your interest in the probabilities of numbers appearing in specific positions after a result has been sorted. I will need to spend some time writing out a set of instructions to enble readers to construct their own spreadsheet. This would enable them to enter :-

1. How many balls are there in total in their lottery
2. How many balls are drawn on draw day.

This would create 3 tables :-

a) The number of times (taking into account all the possible combinations) that a number can appear in a columnar position after sorting. For example the number 1 can appear 1,712,304 times in first position (6/49 lottery) and not in any other position. The number 2 can appear 1,533,939 times in first place and 178,365 times in second place and nowhere else.

b) From the above The probability of a number appearing in a sorted position.

c) From the above the theoretical average skip value for that number in that sorted position.

This might take a day or two but I'll be back.

 

[emilyng]

Hi PAB

Here is my link to mediafire
http://www.mediafire.com/?yq6sp688mcl2mmv

Hope this can clear your doubt.

Hi Frank,

Thanks for the sharing!

 

[PAB]

Hi emilyng,

Firstly ...

Hope this can clear your doubt.

... it is NOT my doubt but the explanation and interpretation of what you require.

Secondly, unfortunately the link you posted is showing ...

Invalid or Deleted File.

Can you please try to upload it again?

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[emilyng]

Hi PAB,

I upload it again, please check link below
http://www.mediafire.com/?zqjuzpeudyfdes5

 

[PAB]

Hi emilyng,

Thanks for uploading the file again.
Unfortunately there is actually something wrong with it.
Am I right in saying that the file you have uploaded is from Gail Howard's program?
I say this because there are "Automatic update of links" within the file.
The file itself reveals no insight into your request and contains no meaningful data that can be used to achieve a resolution.
Perhaps you can upload the file again after checking that the data within it will be useful beforehand.
Thanks in advance.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[emilyng]

Hi PAB,
No. I don't own any program. Let me check my data. Tq