How to calculate lottery combinations by sums

[motilulla]

Hello,

Is there any way to calculate sum combinations?

Simple for example 6/49 lottery total combinations 13.983.816
Using all 49 numbers….
Minimum sum 1+2+3+4+5+6 = 21 (1 combination)
Maximum sum 44+45+46+47+48+49= 279 (1 combination)
Question-1 how much combinations will be find with each sum from 21 to 279

Now for example we fix in position number “1” this bring down combinations to 1.712.304
Question-2 what will be the min maximum sum range and how many and combinations will be find with each sum

Now for example we fix in position number “2” this bring down combinations to 1.533.939
Question-3 what will be the min maximum sum range and how many and combinations will be find with each sum

Please advice does exist any VBA of Formula to work out sums

Thanks In Advance
Using version 2000

Regards,
Moti

 

[StephenCrump]

Try:

Excel 2010
	A	B
1	Sum	Frequency
2	21	1
3	22	1
4	23	2
5	24	3
6	25	5
7	26	7
8	27	11
9	28	14
10	29	20
11	30	26
12	31	35
13	32	44
14	33	58
15	34	71
16	.	90
17	271	20
18	272	14
19	273	11
20	274	7
21	275	5
22	276	3
23	277	2
24	278	1
25	279	1
26
27	SUM	13,983,816
Sheet1

Sub Test()
    
    Dim N As Long, r As Long, lResults() As Long, lTally() As Long, lSmall As Long, lLarge As Long
    Dim lTemp As Long, i As Long, j As Long
    
    N = 49
    r = 6
    lSmall = r * (r + 1) / 2
    lLarge = r * (2 * N - r + 1) / 2
    ReDim lTally(lSmall To lLarge, 1 To 2)
    lResults = GetCombinations(N, r)
    For i = lSmall To lLarge
        lTally(i, 1) = i
    Next i
    
    For i = 1 To UBound(lResults)
        lTemp = 0
        For j = 1 To r
            lTemp = lTemp + lResults(i, j)
        Next j
        lTally(lTemp, 2) = lTally(lTemp, 2) + 1
    Next i
    
    Range("A2").Resize(lLarge - lSmall + 1, 2).Value = lTally
    
End Sub
Function GetCombinations(lNumber As Long, lNoChosen As Long) As Long()

    Dim lOutput() As Long, lCombinations As Long
    Dim i As Long, j As Long, k As Long
    
    lCombinations = WorksheetFunction.Combin(lNumber, lNoChosen)
    ReDim lOutput(1 To lCombinations, 1 To lNoChosen)
    
    For i = 1 To lNoChosen
        lOutput(1, i) = i
    Next i
    
    For i = 2 To lCombinations
        For j = 1 To lNoChosen
            lOutput(i, j) = lOutput(i - 1, j)
        Next j
        For j = lNoChosen To 1 Step -1
            lOutput(i, j) = lOutput(i, j) + 1
            If lOutput(i, j) <= lNumber - (lNoChosen - j) Then Exit For
        Next j
        For k = j + 1 To lNoChosen
            lOutput(i, k) = lOutput(i, k - 1) + 1
        Next k
    Next i
    
    GetCombinations = lOutput
    
End Function

 

[StephenCrump]

Now for example we fix in position number “1” this bring down combinations to 1.712.304
Question-2 what will be the min maximum sum range and how many and combinations will be find with each sum

You need to be clear about what you mean by "position number 1". It sounds like you're talking about the first number drawn?

There are 49 possibilities for the first number drawn, and then there are COMBIN(48,5) = 1,712,304 possible combinations for the next five numbers. But this gives you a total of 83,902,896 possibilities, compared to the previously calculated 13,983,816. Effectively you've over-counted by a factor of 6, because any single "combination" will have six possible "position number 1" values, e.g. the "combination" 1,2,3,4,5,6 can be any of:

1,2,3,4,5,6
2,1,3,4,5,6
3,1,2,4,5,6
4,1,2,3,5,6
5,1,2,3,4,6
6,1,2,3,4,5

If you constrain the smallest of the numbers to be 15, say, then there will be COMBIN(34,5) = 278,256 possibilities for the remaining five numbers. You can use my code to generate the total for these five as:

Excel 2010
	A	B
1	Sum	Frequency
2	15	1
3	16	1
4	17	2
5	18	3
6	19	5
7	20	7
8	21	10
9	22	13
10
11	151	23
12	152	18
13	153	13
14	154	10
15	155	7
16	156	5
17	157	3
18	158	2
19	159	1
20	160	1
21
22	SUM	278,256
Sheet1

Given that each of the five numbers starts from 16, rather than 1, we'll need to add 5 x 15 = 75 to the totals, i.e. the possible sums will range from 90 to 235.

It should be relatively simple to write a macro to loop through variable values, rather than the fixed 15 used as an example here.

 

[motilulla]

Try:

StephenCrump, Amazing I never thought frequency of sum could be calculated without generating all sets it is nice I like it very much.

Thank you very much for your kind help

Regards,
Moti

 

[motilulla]

If you constrain the smallest of the numbers to be 15, say, then there will be COMBIN(34,5) = 278,256 possibilities for the remaining five numbers. You can use my code to generate the total for these five as:

Hello StephenCrump, I tried to run code changing N = 49 To 34 & r = 6 To 5 it gives me sums total = 324.362
Instead 278.256 result attach…

Book1
	A	B
1	Sum	Frequency
2	15	1
3	16	1
4	17	2
5	18	3
6	19	5
7	20	7
8	21	10
9	22	13
10	.....
11	......
12	156	23
13	157	18
14	158	13
15	159	10
16	160	7
17	161	5
18	162	3
19	163	2
20	164	1
21	165	1
22
23	SUM	324.632
34,5

You need to be clear about what you mean by "position number 1". It sounds like you're talking about the first number drawn?

I mean by "position number 1" for example lottery 6/49, each set is drawn with 6 number out 49 so 6 numbers 6 position this mean 2^nd number is always grater then 1^st, 3^rd is grater then 2^nd …so on

Numbers limit by positions…
Numbers limit in position 1 = 1 to 44
Numbers limit in position 2 = 2 to 45
Numbers limit in position 3 = 3 to 46
Numbers limit in position 4 = 4 to 47
Numbers limit in position 5 = 5 to 48
Numbers limit in position 6 = 6 to 49

Now say for example Position 1 using all numbers 1 to 44, 1^st set = 1-2-3-4-5-6 & last set will be = 44-45-46-47-48-49
But say for example Position 1 using only number 1, in this case 1^st set = 1-2-3-4-5-6 & last set will be = 1-45-46-47-48-49 (using number 1 as a fix number in position 1 total combinations = 1.712.304)

1^st set sum = 21 and the last set sum = 236 so how much sum frequency would be the with 21 to 236

Example-2 say for example Position 1 using only number 2, in this case 1^st set = 2-3-4-5-6-7 & last set will be = 2-45-46-47-48-49 (using number 2 as a fix number in position 1 total combinations = 1.533.939)

1^st set sum = 27 and the last set sum = 237 so how much sum frequency would be the with 27 to 237

What is possibility of sum frequency fixing numbers by positions?

Hope this help

Regards,
Moti

 

[StephenCrump]

Amazing I never thought frequency of sum could be calculated without generating all sets ...

In theory, you could loop from 21 to 279 calculating the combinations that add to these totals. But by the time you do that (and bother to code it!) you might as well loop through every combination, which is what my code does reasonably quickly.

Function GetCombinations returns all COMBIN(49,6) possibilities.

I tried to run code changing N = 49 To 34 & r = 6 To 5 it gives me sums total = 324.362
Instead 278.256 result attach…

My code correctly calculates 278,256. If your last total is 165, you must be working with N=35 not 34 (COMBIN(35,5) = 324,632)

What is possibility of sum frequency fixing numbers by positions?

Try this. Sample results are shown for a small example N=10, r = 4

Excel 2010
	A	B	C	D	E	F	G	H
1	Sum	Frequencies
2		1 first	2 first	3 first	4 first	5 first	6 first	7 first
3	10	1	0	0	0	0	0	0
4	11	1	0	0	0	0	0	0
5	12	2	0	0	0	0	0	0
6	13	3	0	0	0	0	0	0
7	14	4	1	0	0	0	0	0
8	15	5	1	0	0	0	0	0
9	16	7	2	0	0	0	0	0
10	17	7	3	0	0	0	0	0
11	18	8	4	1	0	0	0	0
12	19	8	5	1	0	0	0	0
13	20	8	6	2	0	0	0	0
14	21	7	6	3	0	0	0	0
15	22	7	6	4	1	0	0	0
16	23	5	6	4	1	0	0	0
17	24	4	5	5	2	0	0	0
18	25	3	4	4	3	0	0	0
19	26	2	3	4	3	1	0	0
20	27	1	2	3	3	1	0	0
21	28	1	1	2	3	2	0	0
22	29	0	1	1	2	2	0	0
23	30	0	0	1	1	2	1	0
24	31	0	0	0	1	1	1	0
25	32	0	0	0	0	1	1	0
26	33	0	0	0	0	0	1	0
27	34	0	0	0	0	0	0	1
28
29	210	84	56	35	20	10	4	1
Sheet1

Sub Test2()
    
    Dim N As Long, r As Long, lResults() As Long, lTally() As Long, lSmall As Long, lLarge As Long
    Dim lTemp As Long, i As Long, j As Long, lFirst As Long
    Dim rngPaste As Range
    
    N = 49
    r = 6
    lSmall = r * (r + 1) / 2
    lLarge = r * (2 * N - r + 1) / 2
    ReDim lTally(lSmall To lLarge, 1 To N - r + 2)
    For i = lSmall To lLarge
        lTally(i, 1) = i
    Next i
    
    Set rngPaste = Range("A1")
    On Error Resume Next
    Range("Tallies").Clear
    On Error GoTo 0
    With rngPaste
        .Value = "Sum"
        .Offset(, 1).Value = "Frequencies"
        With .Offset(1, 1).Resize(, UBound(lTally, 2) - 1)
            .Formula = "=Column()-Column(" & rngPaste.Address(, True) & ")"
            .NumberFormat = "0"" first"""
        End With
        .Resize(2).EntireRow.Font.Bold = True
    End With
    
    For lFirst = 1 To N - r + 1
        
        lResults = GetCombinations(N - lFirst, r - 1)
        
        For i = 1 To UBound(lResults)
            lTemp = 0
            For j = 1 To r - 1
                lTemp = lTemp + lResults(i, j)
            Next j
            lTally(lTemp + r * lFirst, 1 + lFirst) = lTally(lTemp + r * lFirst, 1 + lFirst) + 1
        Next i
    
    Next lFirst
    
    With rngPaste
        .Resize(lLarge - lSmall + 3, UBound(lTally, 2)).Name = "Tallies"
        .Offset(2).Resize(lLarge - lSmall + 1, UBound(lTally, 2)).Value = lTally
    End With
    
End Sub

 

[motilulla]

In theory, you could loop from 21 to 279 calculating the combinations that add to these totals. But by the time you do that (and bother to code it!) you might as well loop through every combination, which is what my code does reasonably quickly. Function GetCombinations returns all COMBIN(49,6) possibilities.

StephenCrump, very clever code it is faster, the time it takes just blinks the eyes and results are displayed it is just Amazing!

My code correctly calculates 278,256. If your last total is 165, you must be working with N=35 not 34 (COMBIN(35,5) = 324,632)

I apologize I had in my mind 34 but I put 35 it is correct my mistake.

Try this. Sample results are shown for a small example N=10, r = 4

Sub Test2()
End Sub

I was thinking when I would receive the code, I will have to find out sum one by one but this is wow! What a nice thought I just run the code and get surprised all sum listed by their position at once really wonderful.

Thank you very much for your kind help

Have a great evening

Regards,
Moti

 

[motilulla]

StephenCrump, the Opening post is solved, below is a new question. Sorry to trouble you again

Please I have one more question what if I want to get list frequency of sums excluding these numbers (2, 12,14,22,37,40,42,45,48) " in example I tried to show only 9 number but can be more or less "

I mean I do not want above number could be the part of combinations does it is possible to get sum list as Sub Test2

Regards,
Moti

 

[StephenCrump]

Please I have one more question what if I want to get list frequency of sums excluding these numbers (2, 12,14,22,37,40,42,45,48) " in example I tried to show only 9 number but can be more or less "

Try this revised code. Here's the sample output for a small example, where total combinations = COMBIN(10-3,4) = 35:

N = 10
r = 4
'If no gaps, comment out next line
vGaps = Array(1, 3, 9)

Excel 2020
	A	B	C	D	E
1	Sum	Frequencies
2		2 first	4 first	5 first	6 first
3	17	1	0	0	0
4	18	1	0	0	0
5	19	2	0	0	0
6	20	2	0	0	0
7	21	3	0	0	0
8	22	2	1	0	0
9	23	3	1	0	0
10	24	2	1	0	0
11	25	2	2	0	0
12	26	1	1	1	0
13	27	1	2	0	0
14	28	0	1	1	0
15	29	0	1	1	0
16	30	0	0	1	0
17	31	0	0	0	1
18
19	35	20	10	4	1
Sheet1

Sub Test3()
    
    Dim N As Long, r As Long, lResults() As Long, lTally() As Long, lSmall As Long, lLarge As Long
    Dim lTemp As Long, i As Long, j As Long, lFirst As Long, lNumbersToUse() As Long, lCount As Long, lNoGaps As Long
    Dim rngPaste As Range
    Dim vGaps As Variant
    
    N = 49
    r = 6
    'If no gaps, comment out next line
    vGaps = Array(2, 12, 14, 22, 37, 40, 42, 45, 48)
    On Error Resume Next
    lNoGaps = UBound(vGaps) - LBound(vGaps) + 1
    On Error GoTo 0
    ReDim lNumbersToUse(1 To N - lNoGaps)
    For i = 1 To N
        If IsError(Application.Match(i, vGaps, False)) Then
            lCount = lCount + 1
            lNumbersToUse(lCount) = i
        End If
    Next i
    For i = 1 To r
        lSmall = lSmall + lNumbersToUse(i)
        lLarge = lLarge + lNumbersToUse(N + 1 - lNoGaps - i)
    Next i
    
    N = N - lNoGaps
    ReDim lTally(lSmall To lLarge, 1 To N - r + 2)
    For i = lSmall To lLarge
        lTally(i, 1) = i
    Next i
    
    Set rngPaste = Range("A1")
    On Error Resume Next
    Range("Tallies").Clear
    On Error GoTo 0
    With rngPaste
        .Value = "Sum"
        .Offset(, 1).Value = "Frequencies"
        .Offset(1, 1).Resize(, UBound(lTally, 2) - 1).NumberFormat = "0"" first"""
        For i = 2 To UBound(lTally, 2)
            .Cells(2, i).Value = lNumbersToUse(i - 1)
        Next i
        .Resize(2).EntireRow.Font.Bold = True
    End With
    
    For lFirst = 1 To N - r + 1
        lResults = GetCombinations(N - lFirst, r - 1)
        
        For i = 1 To UBound(lResults)
            lTemp = 0
            For j = 1 To r - 1
                lTemp = lTemp + lNumbersToUse(lFirst + lResults(i, j))
            Next j
            lTally(lTemp + lNumbersToUse(lFirst), 1 + lFirst) = lTally(lTemp + lNumbersToUse(lFirst), 1 + lFirst) + 1
        Next i
    
    Next lFirst
    
    With rngPaste
        .Resize(lLarge - lSmall + 3, UBound(lTally, 2)).Name = "Tallies"
        .Offset(2).Resize(lLarge - lSmall + 1, UBound(lTally, 2)).Value = lTally
    End With
    
End Sub

 

[motilulla]

Try this revised code. Here's the sample output for a small example, where total combinations = COMBIN(10-3,4) = 35:

N = 10
r = 4
'If no gaps, comment out next line
vGaps = Array(1, 3, 9)

Sub Test3()
End Sub

Wow!! StephenCrump, speechless I thank you very much for solving my all request under one thread all codes are working 100% perfect as require

I appreciate your kind help and spending your precious time to solving all my default queries

ALL SOLVED

Good Luck

Regards,
Moti