VBA Repeating triples, quads etc

[simmcity]

Dear Forum's Members, can anyone give me a VBA script that will scan rows of the data and generate for me the most frequent triples, quads, etc (most frequent 3,4,5,6,7,8,9,10 numbers combinations). Keno game 1-80.

Thanks!

p.s. Hope to get feedback from PAB

 

[PAB]

Hi simmcity,
Welcome to the board .

Dear Forum's Members, can anyone give me a VBA script that will scan rows of the data and generate for me the most frequent triples, quads, etc (most frequent 3,4,5,6,7,8,9,10 numbers combinations). Keno game 1-80.

We are talking about some ENORMOUS size combinations here.

The statistics for C(80,n) where n is the number drawn are as follows:-

3 Numbers is 82,160 Combinations.
4 Numbers is 1,581,580 Combinations.
5 Numbers is 24,040,016 Combinations.
6 Numbers is 300,500,200 Combinations.
7 Numbers is 3,176,716,400 Combinations.
8 Numbers is 28,987,537,150 Combinations.
9 Numbers is 231,900,297,200 Combinations.
10 Numbers is 1,646,492,110,120 Combinations .

I have an old program that I used a few years ago that will calculate 3 numbers upto 6 numbers that I could adapt and upload for you. If I remember correctly though it does take a little time to run.

I need to know a couple of things first though.

(1) What cells is your data in?
(2) How many draws are we talking about?
(3) How well do you know VBA?

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.

 

[simmcity]

Hi simmcity,
Welcome to the board .

Thank you, PAB!

We are talking about some ENORMOUS size combinations here.

The statistics for C(80,n) where n is the number drawn are as follows:-

3 Numbers is 82,160 Combinations.
4 Numbers is 1,581,580 Combinations.
5 Numbers is 24,040,016 Combinations.
6 Numbers is 300,500,200 Combinations.
7 Numbers is 3,176,716,400 Combinations.
8 Numbers is 28,987,537,150 Combinations.
9 Numbers is 231,900,297,200 Combinations.
10 Numbers is 1,646,492,110,120 Combinations .

That's true... BUT! Let me tell you what i think... Let's assume we have a data which consist of 4.5k rows. Each row = each draw. (i.e. 4.5k rows of sets of 20 numbers)
So all we need is to generate all possible combinations of (for example) 7 numbers that is laying in array of 4.5k rows. Not all possible ever but all possible in those 4.5k rows. So that's a lot less then numbers you provided above.

I have an old program that I used a few years ago that will calculate 3 numbers upto 6 numbers that I could adapt and upload for you. If I remember correctly though it does take a little time to run.

I would be very thankful if you will share

I need to know a couple of things first though.

(1) What cells is your data in?
(2) How many draws are we talking about?
(3) How well do you know VBA?

Regards,
PAB

(1) From D to W (20 numbers) starting from row #2 to about 4.5k
(2) ~4.5k
(3) Not so good Not a total noob, though

Thanks for the reply, PAB!

- simmcity

 

[simmcity]

So all we need is to generate all possible combinations of (for example) 7 numbers that is laying in array of 4.5k rows and then compare and get repeats out of them. (Sorry, post edit permission expired).

- - simmcity

 

[simmcity]

I've just made some calculations, feel free to correct me if I'm wrong
In set of 20 numbers we have 200 possible sets of 10 numbers (20*10)
Minus "mirror" combinations i.e. 1 2 3, 3 2 1 = 200 - 100 (half of them, not sure, though) = 100.
So we have 100 unique sets of 10 numbers in a singe draw. i.e row.
100*4.500 draws = 450.000 unique sets of 10 numbers.
That's not much for the macro I think.
All it need to do is to compare those 450k combinations with previous draws and find repeats.

 

[PAB]

Thanks for the reply simmcity,

We are talking about some ENORMOUS size combinations here.

The statistics for C(80,n) where n is the number drawn are as follows:-

3 Numbers is 82,160 Combinations.
4 Numbers is 1,581,580 Combinations.
5 Numbers is 24,040,016 Combinations.
6 Numbers is 300,500,200 Combinations.
7 Numbers is 3,176,716,400 Combinations.
8 Numbers is 28,987,537,150 Combinations.
9 Numbers is 231,900,297,200 Combinations.
10 Numbers is 1,646,492,110,120 Combinations .

That's true... BUT! Let me tell you what i think... Let's assume we have a data which consist of 4.5k rows. Each row = each draw. (i.e. 4.5k rows of sets of 20 numbers)
So all we need is to generate all possible combinations of (for example) 7 numbers that is laying in array of 4.5k rows. Not all possible ever but all possible in those 4.5k rows. So that's a lot less then numbers you provided above.

I live in the UK so obviously don't do the Keno Lotto.
I was curious what the statistics were so that's why I did that little exercise, sorry if it was misleading.
If we were to cycle through ALL the combinations I posted the program would run forever.
I had a quick look but I can't seem to find my original program unfortunately that actually did use Arrays as you mentioned.
I will have another look but if I can't find it I will put something together for.
I just watched a video on YouTube on how to play Keno, it seems like quite a good game?

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 simmcity,

I've just made some calculations, feel free to correct me if I'm wrong
In set of 20 numbers we have 200 possible sets of 10 numbers (20*10)
Minus "mirror" combinations i.e. 1 2 3, 3 2 1 = 200 - 100 (half of them, not sure, though) = 100.
So we have 100 unique sets of 10 numbers in a singe draw. i.e row.
100*4.500 draws = 450.000 unique sets of 10 numbers.
That's not much for the macro I think.

You are incorrectly calculating the combinations.
The correct calculation for 10 numbers from 20 is C(20,10).
Put in the Excel formula =COMBIN(20,10) and it will give you 184,756 combinations.

The combinations from 1 number to 10 numbers from 20 numbers is as follows:

1 Number is 20 Combinations.
2 Numbers is 190 Combinations.
3 Numbers is 1,140 Combinations.
4 Numbers is 4,845 Combinations.
5 Numbers is 15,504 Combinations.
6 Numbers is 38,760 Combinations.
7 Numbers is 77,520 Combinations.
8 Numbers is 125,970 Combinations.
9 Numbers is 167,960 Combinations.
10 Numbers is 184,756 Combinations.

All it need to do is to compare those 450k combinations with previous draws and find repeats.

We are ONLY interested in the data we have got, so I will put something together that only cycles through the ACTUAL DATA as opposed to the number of ACTUAL COMBINATIONS.

Minus "mirror" combinations i.e. 1 2 3, 3 2 1 = 200 - 100 (half of them, not sure, though) = 100.

You left out [2 1 3], [2 3 1], [3 1 2], but why is this important when we are using COMBINATIONS, this only applies if we are using PERMUTATIONS?
I thought Keno was a non-replacement of balls ( numbers ) Lotto?

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.

 

[simmcity]

I live in the UK so obviously don't do the Keno Lotto.
I was curious what the statistics were so that's why I did that little exercise, sorry if it was misleading.
If we were to cycle through ALL the combinations I posted the program would run forever.
I had a quick look but I can't seem to find my original program unfortunately that actually did use Arrays as you mentioned.
I will have another look but if I can't find it I will put something together for.
I just watched a video on YouTube on how to play Keno, it seems like quite a good game?

Thanks for the reply, PAB!

Well, Keno is a good game.

Dunno why but I won a lot more than in any other game.
For example if you'll hit the 6 numbers out of 20 you'll get ~$2k to 10k (depends on your bet). It's quite hard but I know a few people who did that a couple of times.
Moreover you can hit 2 numbers out of 2 out of 20 and get from $20 to $100! It's quite easy. For experienced player of course.

 

[simmcity]

Hi simmcity,


You are incorrectly calculating the combinations.
The correct calculation for 10 numbers from 20 is C(20,10).
Put in the Excel formula =COMBIN(20,10) and it will give you 184,756 combinations.

The combinations from 1 number to 10 numbers from 20 numbers is as follows:

1 Number is 20 Combinations.
2 Numbers is 190 Combinations.
3 Numbers is 1,140 Combinations.
4 Numbers is 4,845 Combinations.
5 Numbers is 15,504 Combinations.
6 Numbers is 38,760 Combinations.
7 Numbers is 77,520 Combinations.
8 Numbers is 125,970 Combinations.
9 Numbers is 167,960 Combinations.
10 Numbers is 184,756 Combinations.


We are ONLY interested in the data we have got, so I will put something together that only cycles through the ACTUAL DATA as opposed to the number of ACTUAL COMBINATIONS.


You left out [2 1 3], [2 3 1], [3 1 2], but why is this important when we are using COMBINATIONS, this only applies if we are using PERMUTATIONS?
I thought Keno was a non-replacement of balls ( numbers ) Lotto?

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.

Thanks for the correction PAB!

I thought Keno was a non-replacement of balls ( numbers ) Lotto?

Keno have the same rules as any other lottery game. It's like 6/49 but the pool is 80 and all you need is to get only HALF of the 20 numbers drawn.

- simmcity

 

[PAB]

Hi simmcity,

Thanks for the reply.

Thanks for the correction PAB!

It's not so much a correction as just showing you the correct way to calculate combinations for the future, because as you know, with all the different scenarios for analysing Lotto data the vast majority have a combinations calculation.
We are all here to learn as we often do on a daily basis.

Could you upload a SpreadSheet with ALL the Keno Lotto numbers please so I have got the actual data to test with?

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.

 

[simmcity]

Hi simmcity,

Thanks for the reply.


It's not so much a correction as just showing you the correct way to calculate combinations for the future, because as you know, with all the different scenarios for analysing Lotto data the vast majority have a combinations calculation.
We are all here to learn as we often do on a daily basis.

Could you upload a SpreadSheet with ALL the Keno Lotto numbers please so I have got the actual data to test with?

Thanks in advance.

Regards,
PAB

Thank you for sharing such wise words!

a SpreadSheet:

vitaminaoverdose.net/keno.xls

- simmcity

 

[simmcity]

PAB I KNOW there is a few repeated sets of 10 numbers. And you know... Everything that happens once can never happen again. But everything that happens twice will surely happen a third time. (c)

 

[PAB]

Hi simmcity,

I have the SpreadSheet, thanks for that.
Which program analysis would you like first, the 3, 4, 5, 6, 7, 8, 9 OR 10 numbers?

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.

 

[simmcity]

Hi PAB,

let's start from ten Then 7

sincere thanks for the selfless help, I owe you a Maserati

- simmcity

 

[PAB]

Hi PAB,

let's start from ten Then 7
sincere thanks for the selfless help, I owe you a Maserati

OK, I will try and put something together later for you or if not tomorrow.

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 simmcity,

Well, Keno is a good game.

Dunno why but I won a lot more than in any other game.
For example if you'll hit the 6 numbers out of 20 you'll get ~$2k to 10k (depends on your bet). It's quite hard but I know a few people who did that a couple of times.
Moreover you can hit 2 numbers out of 2 out of 20 and get from $20 to $100! It's quite easy. For experienced player of course.

Out of interest have you been doing the Keno Lotto since it started on the 11-04-2001?
If so, what sort of percentage success rate would you say you have attained?

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.

 

[simmcity]

Hi PAB,

Honestly I first began playing Keno a few years ago. Anyway. I had once 8/10.

- simmcity

 

[PAB]

Hi simmcity,

So all we need is to generate all possible combinations of (for example) 10 numbers that is laying in the array of 4.5k rows. Not all possible ever but all possible in those 4.5k rows.
That's not much for the macro I think.

If only it was that EASY .

Well, I have spent quite a few hours on this over the last couple of days and have come to the conclusion that although this can be done the physical time for the program to compile is horrendous.
I tested it on 1, 2, 3, 4, 5 & 10 combinations which gave the results below ( using 1 Gigabyte of RAM ).

01 combination run in 10 seconds and obviously produced 184,756 results which is correct for C(20,10).
02 combinations run in 26 seconds.
03 combinations run in 51 seconds.
04 combinations run in 1 minute & 28 seconds.
05 combinations run in 2 minutes & 38 seconds.
10 combinations run in 21 minutes & 6 seconds and produced 1,847,560 results.

You can see from the above results where this is going .
We cannot even evaluate the FULL run-time because the differences from one combination to another is ever increasing because of the extra data being written to the Array each time a new combination is added, making a total of billions of iterations.

I might try it latter using the same test criteria above but for C(20,5) which initially gives us 15,504 combinations for 1 combination and post the results.

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.

 

[simmcity]

Hi, PAB

Thank you for all your help! And thanks for sharing such an interesting information.

So there is no way to solve this issue?

Or maybe there's another way to solve this... Whats your professional opinion?

- simmcity

 

[Frank]

Heres my take on it, for what its worth. First let me say that PAB's assessment of the situation is spot on. I agree with his mathematical assessment of what we are up against precisely. The problem is that even though we confine our macro to dealing with the data available, the number of required unique items of collected data we need to store still has to be available to cover the whole range of possible outcomes (some as yet unknown) for pairing a number n1 with number n2 where for any new result set, n1 can be as low as 1 or as high as 80. And thats just for pairs, the reasoning continues for triples quads etc right up the range of families of results as far as groupls of 10 from 20 per result. Without resorting to macros I looked at the problem from the point of view of what was the likelihood of finding any useful data at the end of such an excercise.
The Keno is new to me as well, since I too come from the UK so I've studied the various odds involved in the game.
I know that one can choose how many numbers from the 20 drawn you wish to match and have to then specify that number of balls in your bet. This probably old hat to our US based friends so forgive me for going through this again as its new to me, I'm sure you've seen all this.

To work out the odds on a spreadsheet for all the various choices of numbers of balls to match you can use the HYPOGEOMDIST function. =HYPOGEOMDIST(number succeeeded in matching, number you promised to match, number of balls drawn, highest ball number ).

So for The Keno if you want to match 10 balls the probability is =HYPOGEOMDIST(10,10,20,80) = 1.12212E-07 giving odds of 8,911,710.18 to 1.

If you are choosing to match 4 balls the same formula would be =HYPOGEOMDIST(4,4,20,80)= 0.003063392 giving odds of 325.4355 to 1.
If you are feeling keen you can (and probably already have) created a table in this way for 1,2,3,4,5,6,7,8,9,10 balls you hope to match.

I've uploaded mine for reference anyway. ..http://www.mediafire.com/view/?rdb9z6ctz736oou

Why is this relevant to the exercise we are trying to perform ?

Well each row of results in your 4.5k rows of results can be thought of as a 20 number entry into the draw. All the other results can be thought of as draws you tried to match that result with. The odds are exactly the same as if you were playing the game and are as calculated above. The only difference is each row of 20 numbers can be regarded as a full wheel of pairs or triples or quads or quins etc. to match against all the other rows of results. I cannot see simmcity's data spreadsheet as it appears to be no longer available (it doesn't matter) but you can imagine if you were counting all the triples of a specific combination say 12,35,77 then you are counting them up over your 4.5k lines of 20 numbers and each new result set gives you =COMBIN(20,3) =1140 chances of finding that combination. Over 4500 draws you have 4500 x 1140 = 5130000 chances of finding it . The computed odds in Keno if you were entering those three numbers would be 71.072 to 1 of finding it in a single draw. However we are comparing a 20 number wheel containing the required 3 so those odds are optimistic by a factor of =COMBIN(20,3) =1140. All this leads to a conclusion that I would expect to find 5130000/1140 = about (on average) 63 occurrences of that pair. This would be useful information when compared with other triples so would be worth doing.

Contrast that with quints. Doing the same exercise but this time taking the short cut of multiplying the probability of matching 5 (p= 0.000644925) by 4500 comes to an expected occurence (in 4500 results) of 2.9. Over 4500 lines of results I would expect to find 2.9 ocurrences of a given quint and many others wouldn't be found yet. Is this worthwhile information to compare ? NO.
Furthermore, the more numbers in the n-tuplet you want data for e.g a family of 10, the LOWER the expectation of finding that n-tuplet in your data. Youd need 10 MILLION draws to fine ONE named family of 10.
So its not worth the effort in my opinion, no useful information would be found anyway.
Thats my view, I hope I've got my sums right, no doubt someone will tell me if not.