Excel Formulas for Consecutive Numbers

P

PAB

Member
Hi Everyone,

Does Anyone Have the Excel Formulas to Work Out the Following Distributions ( For ALL 13,983,816 Combinations ) of Consecutive Numbers in a 649 Lotto Please :-

111111 = NO Consecutives
211110 = 1 Set of 2 Consecutives
221100 = 2 Sets of 2 Consecutives
222000 = 3 Sets of 2 Consecutives
311100 = 1 Set of 3 Consecutives
321000 = 1 Set of 3 Consecutives & 1 Set of 2 Consecutives
330000 = 2 Sets of 3 Consecutives
411000 = 1 Set of 4 Consecutives
420000 = 1 Set of 4 Consecutives & 1 Set of 2 Consecutives
510000 = 1 Set of 5 Consecutives
600000 = 1 Set of 6 Consecutives

I Obtained the Following Totals ( For ALL 13,983,816 Combinations ) for EACH Distribution some Time Ago from Somewhere ( I think it Might have Been GillesD ), But would Ideally like the Excel Formulas for EACH Please :agree: .

111111 = 7,059,052
211110 = 5,430,040
221100 = 814,506
222000 = 13,244
311100 = 543,004
321000 = 79,464
330000 = 946
411000 = 39,732
420000 = 1,892
510000 = 1,892
600000 = 44
Total Combinations = 13,983,816

Thanks in Advance.
All the Best
PAB
:wavey:
 
Last edited:
G

GillesD

Member
Consecutive numbers sequence

I use the advanced filtering technique in Excel to calculate frequency of each possibility from 1-1-1-1-1-1 to 6-0-0-0-0-0.

The criteria changes for each sequence and if data on winning numbers is in columns C to H in increasing order, it could as simple as:

H5-C5=5 for 6 consecutive numbers (6-0-0-0-0-0)

or still easy to understand such as:

AND(E5-C5=2,H5-F5=2,F5-E5>1) for 2 sets of 3 consecutive numbers (3-3-0-0-0-0)

or a little more complex like:

AND(D5-C5>1,E5-D5>1,F5-E5>1,G5-F5>1,H5-G5>1) for no consecutive numbers (1-1-1-1-1-1).

And this is certainly not the most complex one as you have to look for consecutive numbers at all positive positions.
 
G

GillesD

Member
Consecutive numbers

PAB

The data you quoted is for all possible 13,983,816 combinations of a 6/49 lottery. The actual data for Lotto 6/49 after 2,189 draws is:

No consecutive: 1,127 combinations or 51.48%
1 double: 828 combinations or 37.83%
2 doubles: 144 combinations or 6.58%
3 doubles: 1 combination or 0.05%
1 triple: 66 combinations or 3.02%
1 triple and 1 double: 16 combinations or 0.73%
2 triples: 0 combination or 0.00%
1 quadruple: 6 combinations or 0.27%
1 quadruple and 1 double: 0 combinations or 0.00%
1 quintuple: 1 combination or 0.05%
1 sextuple: 0 combinations or 0.00%
 
P

PAB

Member
Thanks GillesD

Thanks for the Reply GillesD,

Unfortunately I did NOT Explain myself as Well as I could of, my Appologies. I have Edited my Original Post with what I Should Have put in the First Place.
It is Basically Using ALL the 13,983,816 Combinations of a 649 Lotto and Coming up with the Formulas for EACH Distribution that will Return the Results Below :-

111111 = 7,059,052
211110 = 5,430,040
221100 = 814,506
222000 = 13,244
311100 = 543,004
321000 = 79,464
330000 = 946
411000 = 39,732
420000 = 1,892
510000 = 1,892
600000 = 44
Total Combinations = 13,983,816

Thanks Again.
All the Best
PAB
:wavey:
 
Last edited:
G

GillesD

Member
Consecutive numbers

This would require a slightly different approach unless you list all combinations and then apply the technique described in my post.

To obtain the data I posted a long time ago, I ran the macro listed below:

Option Explicit
Option Base 1
Sub Compte_Cons()
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer, F As Integer
Dim nType(11) As Double, I As Integer
Application.ScreenUpdating = False
Sheets("Sommaire").Select
For I = 1 To 11
nType(I) = 0
Next I
For A = 1 To 44
For B = A + 1 To 45
For C = B + 1 To 46
For D = C + 1 To 47
For E = D + 1 To 48
For F = E + 1 To 49
If B - A > 1 And C - B > 1 And D - C > 1 And E - D > 1 And F - E > 1 Then nType(1) = nType(1) + 1
If B - A = 1 And C - B > 1 And D - C > 1 And E - D > 1 And F - E > 1 Then nType(2) = nType(2) + 1
If B - A > 1 And C - B = 1 And D - C > 1 And E - D > 1 And F - E > 1 Then nType(2) = nType(2) + 1
If B - A > 1 And C - B > 1 And D - C = 1 And E - D > 1 And F - E > 1 Then nType(2) = nType(2) + 1
If B - A > 1 And C - B > 1 And D - C > 1 And E - D = 1 And F - E > 1 Then nType(2) = nType(2) + 1
If B - A > 1 And C - B > 1 And D - C > 1 And E - D > 1 And F - E = 1 Then nType(2) = nType(2) + 1
If B - A = 1 And C - B > 1 And D - C = 1 And E - D > 1 And F - E > 1 Then nType(3) = nType(3) + 1
If B - A = 1 And C - B > 1 And D - C > 1 And E - D = 1 And F - E > 1 Then nType(3) = nType(3) + 1
If B - A = 1 And C - B > 1 And D - C > 1 And E - D > 1 And F - E = 1 Then nType(3) = nType(3) + 1
If B - A > 1 And C - B = 1 And D - C > 1 And E - D = 1 And F - E > 1 Then nType(3) = nType(3) + 1
If B - A > 1 And C - B = 1 And D - C > 1 And E - D > 1 And F - E = 1 Then nType(3) = nType(3) + 1
If B - A > 1 And C - B > 1 And D - C = 1 And E - D > 1 And F - E = 1 Then nType(3) = nType(3) + 1
If B - A = 1 And C - B > 1 And D - C = 1 And E - D > 1 And F - E = 1 Then nType(4) = nType(4) + 1
If C - A = 2 And D - C > 1 And E - D > 1 And F - E > 1 Then nType(5) = nType(5) + 1
If B - A > 1 And D - B = 2 And E - D > 1 And F - E > 1 Then nType(5) = nType(5) + 1
If B - A > 1 And C - B > 1 And E - C = 2 And F - E > 1 Then nType(5) = nType(5) + 1
If B - A > 1 And C - B > 1 And D - C > 1 And F - D = 2 Then nType(5) = nType(5) + 1
If C - A = 2 And D - C > 1 And E - D = 1 And F - E > 1 Then nType(6) = nType(6) + 1
If C - A = 2 And D - C > 1 And E - D > 1 And F - E = 1 Then nType(6) = nType(6) + 1
If B - A > 1 And D - B = 2 And E - D > 1 And F - E = 1 Then nType(6) = nType(6) + 1
If B - A = 1 And C - B > 1 And E - C = 2 And F - E > 1 Then nType(6) = nType(6) + 1
If B - A = 1 And C - B > 1 And D - C > 1 And F - D = 2 Then nType(6) = nType(6) + 1
If B - A > 1 And C - B = 1 And D - C > 1 And F - D = 2 Then nType(6) = nType(6) + 1
If C - A = 2 And D - C > 1 And F - D = 2 Then nType(7) = nType(7) + 1
If D - A = 3 And E - D > 1 And F - E > 1 Then nType(8) = nType(8) + 1
If B - A > 1 And E - B = 3 And F - E > 1 Then nType(8) = nType(8) + 1
If B - A > 1 And C - B > 1 And F - C = 3 Then nType(8) = nType(8) + 1
If D - A = 3 And E - D > 1 And F - E = 1 Then nType(9) = nType(9) + 1
If B - A = 1 And C - B > 1 And F - C = 3 Then nType(9) = nType(9) + 1
If E - A = 4 And F - E > 1 Then nType(10) = nType(10) + 1
If B - A > 1 And F - B = 4 Then nType(10) = nType(10) + 1
If F - A = 5 Then nType(11) = nType(11) + 1
Next F
Next E
Next D
Next C
Next B
Next A
Range("J52").Select
For I = 1 To 11
ActiveCell.Offset(I, 0).Value = nType(I)
Next I
End Sub

If you want to run this macro, you should make the following adjustments:

- change "Sommaire" to your actual sheet name in the line Sheets("Sommaire").Select (near the top); possibly this could be Sheet1;

- change "J52" to the cell you want to list all values in the line Range("J52").Select (near the bottom); it could very well be cell B1 and in cells A1 to A11, list all combinations (1-1-1-1-1-1, etc.).

It might not be the most elegant or efficient macro, but it gets me the results I want.
 
P

PAB

Member
Thank You GillesD,

Outstanding as Usual :agree: , it Ran in About 2 Minutes and Agreed Exactly with the Results that I Listed in my Original Post :agree2: . I will take a Look through the Program over the Weekend and Maybe try and Create One for ALL Possible Decades.

Thanks Again.
Have a Great Weekend.
All the Best
PAB
:wavey:
 
G

GillesD

Member
Grouping by decades

PAB, glad I could help you.

For your latest enquiry, I will tell you this:

- modifying the macro will most likely work but this time, I used only formulas with the COMBIN function to calculate the number of combinations for each of the 10 possibilities from 2-1-1-1-1 to 6-0-0-0-0.

You choose whatever approach you like, both will require a systematic approach to cover all possibilities. This is somewhat complicated by the fact that there are 9 numbers in the first decade and 10 in the next four.
 
P

PAB

Member
Thanks for the Reply GillesD,

From Reading your Post I Gather that you have Already Produced the Decades Distribution Totals Using Excel Formulas ( Using the COMBIN Function to Calculate the Number of Combinations for EACH of the 10 Possibilities ).
I Realise what you Meant when you said it will Require a Systematic Approach to Cover ALL Possibilities, Especially with the Added Complication of the Fact that there are 9 Numbers in the First Decade and 10 in the Next Four :dizzy: .
Regardless of that, I Decided to have a go at Producing the Excel Formulas for Decades ( Using a Systematic Approach ), and came up with the Following. Could you Please have a Look through them and see if there is Anything that Jumps Out at you. Here they are :-

For Distribution 21111 = 1,980,000 Combinations
=COMBIN(9,2)*COMBIN(4,4)*COMBIN(10,1)*COMBIN(10,1)*COMBIN(10,1)*COMBIN(10,1)+COMBIN(9,1)*COMBIN(4,1)*COMBIN(10,2)*COMBIN(3,3)*COMBIN(10,1)*COMBIN(10,1)*COMBIN(10,1)

For Distribution 22110 = 5,346,000 Combinations
=COMBIN(4,2)*COMBIN(10,2)*COMBIN(10,2)*COMBIN(2,2)*COMBIN(10,1)*COMBIN(10,1)+COMBIN(1,1)*COMBIN(9,2)*COMBIN(4,1)*COMBIN(10,2)*COMBIN(3,2)*COMBIN(10,1)*COMBIN(10,1)+COMBIN(9,1)*COMBIN(4,2)*COMBIN(10,2)*COMBIN(10,2)*COMBIN(2,1)*COMBIN(10,1)

For Distribution 22200 = 801,900 Combinations
=COMBIN(4,3)*COMBIN(10,2)*COMBIN(10,2)*COMBIN(10,2)+COMBIN(1,1)*COMBIN(9,2)*COMBIN(4,2)*COMBIN(10,2)*COMBIN(10,2)

For Distribution 31110 = 2,112,000 Combinations
=COMBIN(4,1)*COMBIN(10,3)*COMBIN(3,3)*COMBIN(10,1)*COMBIN(10,1)*COMBIN(10,1)+COMBIN(1,1)*COMBIN(9,3)*COMBIN(4,3)*COMBIN(10,1)*COMBIN(10,1)*COMBIN(10,1)+COMBIN(9,1)*COMBIN(4,1)*COMBIN(10,3)*COMBIN(3,2)*COMBIN(10,1)*COMBIN(10,1)

For Distribution 32100 = 2,851,200 Combinations
=COMBIN(4,1)*COMBIN(10,3)*COMBIN(3,1)*COMBIN(10,2)*COMBIN(2,1)*COMBIN(10,1)+COMBIN(1,1)*COMBIN(9,3)*COMBIN(4,1)*COMBIN(10,2)*COMBIN(3,1)*COMBIN(10,1)+COMBIN(9,2)*COMBIN(4,1)*COMBIN(10,3)*COMBIN(3,1)*COMBIN(10,1)+COMBIN(9,1)*COMBIN(4,1)*COMBIN(10,3)*COMBIN(3,1)*COMBIN(10,2)

For Distribution 33000 = 126,720 Combinations
=COMBIN(4,2)*COMBIN(10,3)*COMBIN(10,3)+COMBIN(1,1)*COMBIN(9,3)*COMBIN(4,1)*COMBIN(10,3)

For Distribution 41100 = 554,400 Combinations
=COMBIN(4,1)*COMBIN(10,4)*COMBIN(3,2)*COMBIN(10,1)*COMBIN(10,1)+COMBIN(1,1)*COMBIN(9,4)*COMBIN(4,2)*COMBIN(10,1)*COMBIN(10,1)+COMBIN(9,1)*COMBIN(4,1)*COMBIN(10,4)*COMBIN(3,1)*COMBIN(10,1)

For Distribution 42000 = 166,320 Combinations
=COMBIN(4,1)*COMBIN(10,4)*COMBIN(3,1)*COMBIN(10,2)+COMBIN(1,1)*COMBIN(9,4)*COMBIN(4,1)*COMBIN(10,2)+COMBIN(9,2)*COMBIN(4,1)*COMBIN(10,4)

For Distribution 51000 = 44,352 Combinations
=COMBIN(4,1)*COMBIN(10,5)*COMBIN(3,1)*COMBIN(10,1)+COMBIN(1,1)*COMBIN(9,5)*COMBIN(4,1)*COMBIN(10,1)+COMBIN(9,1)*COMBIN(4,1)*COMBIN(10,5)

For Distribution 60000 = 924 Combinations
=COMBIN(4,1)*COMBIN(10,6)+COMBIN(1,1)*COMBIN(9,6)

Total Combinations = 13,983,816

Thanks Again.
All the Best
PAB
:wavey:
 
G

GillesD

Member
Combinations for decades

PAB

I hope you found the exercise interesting and it helped increase your skills in Excel.

For this exam, I will give you a rating of 100%. I checked only the answers (not all formulas) but since they all agree with my own, I will assume they are not that bad.
 
P

PAB

Member
Thanks VERY Much GillesD,

I Even went Mad and Worked out the Formulas for Dozens Distribution :agree: .
There is One thing that I am Sure you will be Able to Help me with Please. Do you have a Macro that Counts ( Using ALL the 13,983,816 Combinations ) the Root of Sum of Individual Numbers for EACH Combination Please.

There are 11 Distributions which are as Follows :-
111111
211110
221100
222000
311100
321000
330000
411000
420000
510000
600000

There are 9 Groups for Root of Sum. I have Condensed the List of 49 Numbers Down into the Table Below, with the Numbers for EACH Group in Ascending Order :-

Group 1 = 01, 10, 19, 28, 37, 46 Root Sum = 1
Group 2 = 02, 11, 20, 29, 38, 47 Root Sum = 2
Group 3 = 03, 12, 21, 30, 39, 48 Root Sum = 3
Group 4 = 04, 13, 22, 31, 40, 49 Root Sum = 4
Group 5 = 05, 14, 23, 32, 41 Root Sum = 5
Group 6 = 06, 15, 24, 33, 42 Root Sum = 6
Group 7 = 07, 16, 25, 34, 43 Root Sum = 7
Group 8 = 08, 17, 26, 35, 44 Root Sum = 8
Group 9 = 09, 18, 27, 36, 45 Root Sum = 9

The Groups Above are Only for Visual Explanation. Ideally, the Macro will ADD Together the Digits for Ball 1, Ball 2, Ball 3, Ball 4, Ball 5 & Ball 6 for the First Combination, Remember which Distribution it Belongs to, then go to the Second Combination, ADD the Digits for Ball 1, Ball 2, Ball 3, Ball 4, Ball 5 & Ball 6 and so on, Until it Reaches the Last One, then Produce the Totals for EACH of the 11 Distributions.

Example 1 :-
So for Combination 03, 09, 15, 25, 35, 43, the Distribution would be 211110 Because :-
03 = Root Sum 0 + 3 = 3
09 = Root Sum 0 + 9 = 9
15 = Root Sum 1 + 5 = 6
25 = Root Sum 2 + 5 = 7
35 = Root Sum 3 + 5 = 8
43 = Root Sum 4 + 3 = 7
So you have Two Numbers ( 25 & 43 ) from One Group, and the Rest from Different Other Groups.

Example 2 :-
So for Combination 02, 11, 18, 20, 30, 39, the Distribution would be 321000 Because :-
02 = Root Sum 0 + 2 = 2
11 = Root Sum 1 + 1 = 2
18 = Root Sum 1 + 8 = 9
20 = Root Sum 2 + 0 = 2
30 = Root Sum 3 + 0 = 3
39 = Root Sum 3 + 9 = 12 Therefore 1 + 2 = 3
So you have Three Numbers ( 02, 11 & 20 ) from One Group, Two Numbers ( 30 & 39 ) from Another Group and the Last Number from a Different Group.

Thanks in Advance.
All the Best
PAB
:wavey:
 
B

BushHappy

Member
Can 49 & 1 be considered as consecutive Numbers?

If all 49 numbers are spaced evenly along the circumference of a circle in numerical order, you will notice that 1 follows after 49 in the same way as 2 follows after 1.

Can 49 & 1 be considered as consecutive Numbers?

Cheers,

BushHappy
 
P

PAB

Member
Hi BushHappy,

That is a Good Point :agree2: .
I think that Most People would Agree that Number 50 ( in a 649 Lotto ) is the Logical Consecutive Number After Number 49.
The Oxford Dictionary Says that a Consecutive Number is "Following Continuously in an Unbroken Or Logical Order", which is Exactly what would Happen if ALL 49 Numbers were Spaced Evenly Along the Circumference of a Circle in Numerical Order.
I can See the Logic in your Thinking, and would Suggest that this would Possibly be of Benefit for Wheeling Puposes.
I would be Interested if Any of the Members on this Board that have Developed their Own Wheeling Systems use this Scenario in their Programs.

I Hope this Helps.
All the Best
PAB
:wavey:
 
P

PAB

Member
GillesD,

Out of Interest, why Does it Print the Ouput Starting in Cell "A2 "when "A1" has Been Selected in the Program.
One Other thing Please, is it Possible to Put the Total of the Output.

Thanks.
All the Best
PAB
:wavey:
 
Last edited:
P

PAB

Member
Hi GillesD,

Please Don't Worry About the Macro for Root of Sum, I have Calculated the Total Combinations for ALL 11 Distributions Using Excel Formulas, Now that has been an Interesting Challenge.
I would Still like to Know if Possible Please why the Consecutives Macro Prints the Ouput Starting in Cell "A2 "when "A1" has Been Selected in the Program.
One Other thing Please, would it be Possible for you to Amend the Macro so that it Puts the Total Combinations at the End of the Output Please.

Thanks Again :agree2: .
All the Best
PAB
:wavey:
 
G

gsobier

Member
...nope, the total opposite... ...more like missing out an easy opportunity to nail 5+ on the weekend which is really rare and thinking, na, it won't happen, it did:bawl:...
tomtom said:
Hey Gsobier, long time no see...any good news? Some fives or better?
Click to expand...
 
T

tomtom

Member
gsobier said:
...nope, the total opposite... ...more like missing out an easy opportunity to nail 5+ on the weekend which is really rare and thinking, na, it won't happen, it did:bawl:...
Click to expand...

Yeah...I might bet some guys are counting fours now..or more...haven't checked how many winners were there though..
 
You must log in or register to reply here.

Sidebar

Top