Calculating Gaps Between Numbers

P

Paul Black

Hi everyone,

I am trying to create a "Gaps" program that cycles through ALL
13,983,816 combinations in a 6 from 49 Lotto and produces the
following data.
I would like it to work out the Gaps between each of the balls for
EACH combination in a 6 from 49 Lotto with no replacement.
For example, the combination 02 08 18 28 36 45 will have the Gaps
of ...

Ball 1 numbered 02 & Ball 2 numbered 08 = 05 Gaps ( 03 04
05 06 07 )
Ball 2 numbered 08 & Ball 3 numbered 18 = 09 Gaps ( 19 10
11 12 13 14 15 16 17 )
Ball 3 numbered 18 & Ball 4 numbered 28 = 08 Gaps ( 20 21
22 23 24 25 26 27 )
Ball 4 numbered 28 & Ball 5 numbered 36 = 07 Gaps ( 29 30
31 32 33 34 35 )
Ball 5 numbered 36 & Ball 6 numbered 45 = 08 Gaps ( 37 38
39 40 41 42 43 44 )

.... so the Gaps description for this particular combination will be 05
09 08 07 08.

The Gaps difference between ball 1 and ball 2 is really ball 2 minus
ball 1 - 1 and so on.
I would like to have a Gaps description for each category and the
Total combinations for each category associated with that list.
So extracting three combinations from the Loop as an example ...

Combination - 02 09 16 25 38 45
Combination - 04 15 19 36 37 49
Combination - 09 15 28 39 46 47

.... will give the answer ...

Gaps 06 06 08 12 06 = x combinations ( x could be 925,365 combinations
with the exact same Gaps between the balls 1-2, 2-3 ,3-4 ,4-5 & 5-6 )
Gaps 10 03 16 00 11 = x combinations ( x could be 236,258 combinations
with the exact same Gaps between the balls 1-2, 2-3, 3-4, 4-5 & 5-6 )
Gaps 05 12 10 06 00 = x combinations ( x could be 756,258 combinations
with the exact same Gaps between the balls 1-2, 2-3, 3-4, 4-5 & 5-6 )

.... etc.

It would also be nice to have a Total at the bottom of the
combinations list, which of course, should equal 13,983,816
combinations.

I have written the following which has nothing to do with what I am
trying to achieve above, but for some reason it does not work.
It is supposed to give me a list of Gaps from 00 to 43 from ALL
13,983,816 combinations.
The list of Gaps should be in cells B3:B45 and the total combinatios
for each gap should be in cells C3:C45.
Could somebody please have a glance at it.

Option Explicit
Option Base 1

Private Sub Gaps()

Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim i As Integer
Dim GapsTotal(43) As Long

Application.ScreenUpdating = False

For i = 1 To 43
GapsTotal(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 = 0 Then GapsTotal(0) = GapsTotal(0) + 1
If C - B - 1 = 0 Then GapsTotal(0) = GapsTotal(0) + 1
If D - C - 1 = 0 Then GapsTotal(0) = GapsTotal(0) + 1
If E - D - 1 = 0 Then GapsTotal(0) = GapsTotal(0) + 1
If F - E - 1 = 0 Then GapsTotal(0) = GapsTotal(0) + 1

If B - A - 1 = 1 Then GapsTotal(1) = GapsTotal(1) + 1
If C - B - 1 = 1 Then GapsTotal(1) = GapsTotal(1) + 1
If D - C - 1 = 1 Then GapsTotal(1) = GapsTotal(1) + 1
If E - D - 1 = 1 Then GapsTotal(1) = GapsTotal(1) + 1
If F - E - 1 = 1 Then GapsTotal(1) = GapsTotal(1) + 1

.... continued all the way down to ...

If B - A - 1 = 42 Then GapsTotal(42) = GapsTotal(42) + 1
If C - B - 1 = 42 Then GapsTotal(42) = GapsTotal(42) + 1
If D - C - 1 = 42 Then GapsTotal(42) = GapsTotal(42) + 1
If E - D - 1 = 42 Then GapsTotal(42) = GapsTotal(42) + 1
If F - E - 1 = 42 Then GapsTotal(42) = GapsTotal(42) + 1

If B - A - 1 = 43 Then GapsTotal(43) = GapsTotal(43) + 1
If C - B - 1 = 43 Then GapsTotal(43) = GapsTotal(43) + 1
If D - C - 1 = 43 Then GapsTotal(43) = GapsTotal(43) + 1
If E - D - 1 = 43 Then GapsTotal(43) = GapsTotal(43) + 1
If F - E - 1 = 43 Then GapsTotal(43) = GapsTotal(43) + 1

Next F
Next E
Next D
Next C
Next B
Next A

Sheets("Gaps Data").Select

Range("B2").Value = "Gaps"
Range("B3").Value = "Gaps of 00"
Range("B4").Value = "Gaps of 01"

.... continued all the way down to ...

Range("B44").Value = "Gaps of 42"
Range("B45").Value = "Gaps of 43"

Range("C2").Value = "Total"
Range("C3").Select

For i = 1 To 43
ActiveCell.Offset(i, 0).Value = GapsTotal(i)
Next i

Application.ScreenUpdating = True
End Sub

Any help will be greatly appreciated.
Thanks in Advance.
All the Best.
Paul
 
B

Bernie Deitrick

I'm confused, and don't see how you get this:
Gaps 06 06 08 12 06 = x combinations ( x could be 925,365 combinations
with the exact same Gaps between the balls 1-2, 2-3 ,3-4 ,4-5 & 5-6 )
Click to expand...

Those 'gaps' can only be produced by 6 different combinations:
01 08 15 24 37 44
02 09 16 25 38 45
03 10 17 26 39 46
04 11 18 27 40 47
05 12 19 28 41 48
06 13 20 29 42 49

In general, the number of combinations for any gap combinations would be 49 - SUM(Gaps) - 5
For the example above, 49 - 38 - 5 = 6 combinations

There will be lots of combinations of gaps that can only be produced by one combination. For
example:

Gaps 01 01 01 01 01 39

would be uniquely 01 03 05 07 09 49


HTH,
Bernie
MS Excel MVP
 
T

Tom Ogilvy

Bernie:

01 08 15 24 37 44

the numbers with X's represent the GAPS the number of numbers in the GAPS
make up the pattern 06 06 08 12 06. You could repeat this pattern a total
of 6 times until you ran out of numbers.

1
2x
3x
4x 6
5x
6x
7x
8
9x
10x
11x
12x 6
13x
14x
15
16x
17x
18x
19x
20x 8
21x
22x
23x
24
25x
26x
27x
28x
29x
30x 12
31x
32x
33x
34x
35x
36x
37
38x
39x
40x 6
41x
42x
43x
44
45
46
47
48
49
 
B

Bernie Deitrick

Tom,
You could repeat this pattern a total
of 6 times until you ran out of numbers.
Click to expand...

I think we're in agreement:

But I have no idea how the OP got 925,365 combinations:

Bernie
 
P

Paul Black

Thanks for the reply Bernie,

The Gaps 06 06 08 12 06 = x combinations ( x could be 925,365
combinations with the exact same Gaps between the balls 1 & 2, and 2 &
3, and 3 & 4, and 4 & 5, and 5 & 6 ) is only an example figure, I have
no idea what the actual figure is, or come to it, how to code it.

There can only be 5 criteria for each combination. These are the gaps
between balls ...
1 and 2
2 and 3
3 and 4
4 and 5
5 and 6
.... obviously with ALL 13,983,816 combinations there will be quite a
few categories.
The Gaps are produced for each category using all the 6 numbers within
each combination.

Begin Quote :-
Gaps 01 01 01 01 01 39 ( how did you get the 6 sets of
numbers? ) ...
.... would be uniquely 01 03 05 07 09 49
End Quote.

I don't quite understand it when you say ...

Begin Quote :-
Gaps 06 06 08 12 06 = x combinations ( x could be 925,365 combinations
with the exact same Gaps between the balls 1-2, 2-3 ,3-4 ,4-5 & 5-6 )

Those 'Gaps' can only be produced by 6 different combinations :-
01 08 15 24 37 44
02 09 16 25 38 45
03 10 17 26 39 46
04 11 18 27 40 47
05 12 19 28 41 48
06 13 20 29 42 49

In general, the number of combinations for any gap combinations would
be 49 - SUM(Gaps) - 5
For the example above, 49 - 38 - 5 = 6 combinations.
There will be lots of combinations of Gaps that can only be produced
by one combination.
End Quote.

Thanks again.
All the Best.
Paul
 
B

Bernie Deitrick

Paul,

Try the macro below

HTH,
Bernie
MS Excel MVP

Private Sub Gaps2()

Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim i As Integer
Dim GapsTotal(0 To 43) As Long

Application.ScreenUpdating = False

For i = 0 To 43
GapsTotal(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

GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1
GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1
GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1
GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1
GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1

Next F
Next E
Next D
Next C
Next B
Next A

Sheets("Gaps Data").Range("B2").Value = "Gaps"
Sheets("Gaps Data").Range("C2").Value = "Total"

For i = 0 To 43
Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00")
Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i)
Next i

Application.ScreenUpdating = True
End Sub
 
T

Tom Ogilvy

Whoops!
mistook your explantion for part of your question <face red>. Have to
confess I didn't read the original post so thought it was all a quote.
 
P

Paul Black

Thanks for the code Bernie.

I don't have access to Excel at the moment so am unable to test the
code.
Does the code apply to my original request or to my Private Sub Gaps()
effort please. I assume it applies to my effort.
It is my original request that is the most important one.

Thanks in Advance.
All the Best.
Paul
 
B

Bernie Deitrick

Paul,

The macro code is a working version of the macro that you requested, greatly simplified by using
variable indices.

HTH,
Bernie
MS Excel MVP
 
T

Tom Ogilvy

I ran it, here are the results:

Gaps
Gaps of 00 8,561,520
Gaps of 01 7,669,695
Gaps of 02 6,853,770
Gaps of 03 6,108,795
Gaps of 04 5,430,040
Gaps of 05 4,812,990
Gaps of 06 4,253,340
Gaps of 07 3,746,990
Gaps of 08 3,290,040
Gaps of 09 2,878,785
Gaps of 10 2,509,710
Gaps of 11 2,179,485
Gaps of 12 1,884,960
Gaps of 13 1,623,160
Gaps of 14 1,391,280
Gaps of 15 1,186,680
Gaps of 16 1,006,880
Gaps of 17 849,555
Gaps of 18 712,530
Gaps of 19 593,775
Gaps of 20 491,400
Gaps of 21 403,650
Gaps of 22 328,900
Gaps of 23 265,650
Gaps of 24 212,520
Gaps of 25 168,245
Gaps of 26 131,670
Gaps of 27 101,745
Gaps of 28 77,520
Gaps of 29 58,140
Gaps of 30 42,840
Gaps of 31 30,940
Gaps of 32 21,840
Gaps of 33 15,015
Gaps of 34 10,010
Gaps of 35 6,435
Gaps of 36 3,960
Gaps of 37 2,310
Gaps of 38 1,260
Gaps of 39 630
Gaps of 40 280
Gaps of 41 105
Gaps of 42 30
Gaps of 43 5
69,919,080
 
P

Paul Black

Thanks Bernie for producing the code & thanks Tom for running it and
listing the information for me, it is greatly appreciated.
Have either of you had any thoughts on achieving my main request at
all please, that is the part at the beginning of my initial post and
goes down to and includes the paragraph ...

It would also be nice to have a Total at the bottom of the
combinations list, which of course, should equal 13,983,816
combinations.

Thanks in Advance.
All the Best.
Paul
 
B

Bernie Deitrick

Paul,

69,919,080 / 5 =13,983,816


Bernie


Paul Black said:
Thanks Bernie for producing the code & thanks Tom for running it and
listing the information for me, it is greatly appreciated.
Have either of you had any thoughts on achieving my main request at
all please, that is the part at the beginning of my initial post and
goes down to and includes the paragraph ...

It would also be nice to have a Total at the bottom of the
combinations list, which of course, should equal 13,983,816
combinations.

Thanks in Advance.
All the Best.
Paul
Click to expand...
 
P

Paul Black

Hi Bernie,

I think the confusion is me not being able to explain myself properly.
I would like a list of the total combinations for each category of
Gaps calculated for each combination.
For example, taking the combinations below in a 6 from 49 Lotto as ...

Combination 01 02 03 04 05 06 = Gap category 00 00 00 00 00
Combination 01 02 03 04 05 07 = Gap category 00 00 00 00 01
Combination 01 02 03 04 05 08 = Gap category 00 00 00 00 02
Combination 01 02 03 04 05 09 = Gap category 00 00 00 00 03
Combination 01 02 03 04 05 10 = Gap category 00 00 00 00 04
Combination 01 02 03 04 05 11 = Gap category 00 00 00 00 05
Combination 01 02 03 04 05 12 = Gap category 00 00 00 00 06
Combination 01 11 12 14 18 21 = Gap category 09 00 01 03 02
Combination 11 21 22 24 28 31 = Gap category 09 00 01 03 02
Combination 21 22 23 24 25 31 = Gap category 00 00 00 00 05


.... the category list and total combinations for the above will be ...

Category 00 00 00 00 00 = total combinations of 1 ( One )
Category 00 00 00 00 01 = total combinations of 1 ( One )
Category 00 00 00 00 02 = total combinations of 3 ( Three )
Category 00 00 00 00 03 = total combinations of 1 ( One )
Category 00 00 00 00 04 = total combinations of 1 ( One )
Category 00 00 00 00 05 = total combinations of 2 ( Two )
Category 00 00 00 00 06 = total combinations of 1 ( One )

I would also like the total combinations at the end please, which of
course should be 13,983,816 combinations.
I hope this makes it a bit clearer.

Thanks in Advance.
All the Best.
Paul
 
B

Bernie Deitrick

Paul,

Try the macro below.... but try it first with a lower mySize number (see the
comment). It may require a supercomputer to finish, or hours and hours...
13,983,816 combinations is a lot to process.

Bernie


Private Sub Gaps3()

Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim i As Integer
Dim mySize As Integer
Dim strGap As String
Dim myCol As Integer
Dim myRow As Long
Dim myCell As Range

Application.ScreenUpdating = False

myCol = 1
myRow = 1
mySize = 49 'start with a lower number here to try it....

Cells.ClearContents

For A = 1 To mySize - 5
'If A <> 1 Then MsgBox "Finished " & A - 1 & " out of " & mySize - 5
For B = A + 1 To mySize - 4
For C = B + 1 To mySize - 3
For D = C + 1 To mySize - 2
For E = D + 1 To mySize - 1
For F = E + 1 To mySize

strGap = Format(B - A - 1, "00") & " " & _
Format(C - B - 1, "00") & " " & _
Format(D - C - 1, "00") & " " & _
Format(E - D - 1, "00") & " " & _
Format(F - E - 1, "00")
Set myCell = Cells.Find(strGap, , , xlPart)
If myCell Is Nothing Then
Cells(myRow, myCol).Value = strGap & " Count = 1"
myRow = myRow + 1
If myRow = Rows.Count + 1 Then
myRow = 1
myCol = myCol + 1
End If
Else
myCell.Value = strGap & " Count = " & Format(Val(Trim( _
Replace(myCell.Value, strGap & " Count = ", ""))) + 1, "0")
End If

Next F
Next E
Next D
Next C
Next B
Next A

Application.ScreenUpdating = True
End Sub
 
B

Bernie Deitrick

Here's a better macro, with fewer iterations - but still LOTS...

Bernie

Private Sub Gaps4()

Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim i As Integer
Dim mySize As Integer
Dim strGap As String
Dim myCol As Integer
Dim myRow As Long
Dim myCell As Range

Application.ScreenUpdating = False
'
myCol = 1
myRow = 1
mySize = 49 'start with a lower number here to try it....

Cells.ClearContents

For A = 0 To mySize - 5
' If A <> 0 Then MsgBox "Finished " & A & " out of " & mySize - 5
For B = 0 To mySize - 5 - A
For C = 0 To mySize - 5 - B
For D = 0 To mySize - 5 - C
For E = 0 To mySize - 5 - D

If mySize - 5 - A - B - C - D - E > 0 Then
strGap = Format(A, "00") & " " & _
Format(B, "00") & " " & _
Format(C, "00") & " " & _
Format(D, "00") & " " & _
Format(E, "00") & " Count = " & Format(mySize - 5 - A - B - C - D - E,
"0")
Cells(myRow, myCol).Value = strGap
myRow = myRow + 1
If myRow = Rows.Count + 1 Then
myRow = 1
myCol = myCol + 1
End If
End If

Next E
Next D
Next C
Next B
Next A

Application.ScreenUpdating = True
End Sub
 
P

Paul Black

Hi Bernie,

Thanks for the two Subs. As I said before I am unable to test them at
the moment.
The data I posted previously was incorrect, totally my fault. I have
revised it and the correct data that I should have posted is below.

I would like a list of the total combinations for each unique Gaps
category calculated from cycling through ALL 13,983,816 combinations.
For example, taking a few combinations from the 13,983,816
combinations at random in a 6 from 49 Lotto with no replacement of ...

Combination 01 02 03 04 05 06 = Gap category 00 00 00 00 00
Combination 01 02 03 04 05 07 = Gap category 00 00 00 00 01
Combination 01 02 03 04 05 08 = Gap category 00 00 00 00 02
Combination 01 02 03 04 05 09 = Gap category 00 00 00 00 03
Combination 01 02 03 04 05 10 = Gap category 00 00 00 00 04
Combination 01 02 03 04 05 11 = Gap category 00 00 00 00 05
Combination 01 02 03 04 05 12 = Gap category 00 00 00 00 06
Combination 01 11 12 14 18 21 = Gap category 09 00 01 03 02
Combination 11 21 22 24 28 31 = Gap category 09 00 01 03 02
Combination 19 22 23 24 28 31 = Gap category 02 00 00 03 02
Combination 29 32 33 34 38 41 = Gap category 02 00 00 03 02
Combination 31 34 35 36 40 43 = Gap category 02 00 00 03 02

.... the category list and total combinations for the above will be ...

Category 00 00 00 00 00 = total combinations of 1 < One
Category 00 00 00 00 01 = total combinations of 1 < One
Category 00 00 00 00 02 = total combinations of 1 < One
Category 00 00 00 00 03 = total combinations of 1 < One
Category 00 00 00 00 04 = total combinations of 1 < One
Category 00 00 00 00 05 = total combinations of 1 < One
Category 00 00 00 00 06 = total combinations of 1 < One
Category 09 00 01 03 02 = total combinations of 2 < TWO
Category 02 00 00 03 02 = total combinations of 3 < THREE

I would also like the total combinations at the end please, which of
course should be 13,983,816 combinations.
I hope this makes it a bit clearer.

Thanks in Advance.
All the Best.
Paul
 
B

Bernie Deitrick

Paul,

That is what the second macro will do - list all Gaps combinations and their count.

HTH,
Bernie
MS Excel MVP
 
B

Bernie Deitrick

This would be a better loop control structure:

For A = 0 To mySize - 5
For B = 0 To mySize - 5 - A
For C = 0 To mySize - 5 - B - A
For D = 0 To mySize - 5 - C - B - A
For E = 0 To mySize - 5 - D - C - B - A


HTH,
Bernie
MS Excel MVP
 
P

Paul Black

Thanks Bernie,

But how do I apply this to the existing code.

Thanks in Advance.
All the Best.
Paul
 
B

Bernie Deitrick

Paul,

Replace this:

For A = 0 To mySize - 5
' If A <> 0 Then MsgBox "Finished " & A & " out of " & mySize - 5
For B = 0 To mySize - 5 - A
For C = 0 To mySize - 5 - B
For D = 0 To mySize - 5 - C
For E = 0 To mySize - 5 - D

with this

For A = 0 To mySize - 5
For B = 0 To mySize - 5 - A
For C = 0 To mySize - 5 - B - A
For D = 0 To mySize - 5 - C - B - A
For E = 0 To mySize - 5 - D - C - B - A


HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Modify working code to expand the output. 5
Problems with AutoFilter macro 2
Formula help, please. 3
Getting unexpected gaps in a spreadsheet 1
Count Specific Total Numbers from a Set 22
Excel macro programming question 9
Runtime error 13 : type mismatch 2
Loop thru xx No. of sheets until the four strValue combo's are found 6

Top