pythonvbaprobability

Excel number of combinations inside a specific array


Think of this problem similar to a lottery:

I have been trying to determine all possible combinations of 15 numbers (from 1 to 25). By mathematical calculation, I know it is 3.268.760 possibilities.

So, if I played a simple ticket of 15 numbers in the lottery (numbers from 1 to 25), my probability to win would be 3.268.760. The draw is 15 numbers.

Now, I am not sure how to calculate the same probability if I start giving conditions. For example: What is the number of possibilities (and which are they) if I set the condition: 8 odd numbers and 7 even numbers?

Ideally, I need vba to list all the 8 odd numbers and 7 pair numbers combinations from 1 till 25, however, I know this will be over 10 thousand possibilities.

So, what if instead of numbers 1 till 25, I narrowed, say, 20 numbers? would it be possible to randomize the 20 numbers and give me all possible games (15 numbers) which would be 8 odd and 7 even numbers?

This is an example of 5 games which meets my criteria (8 odd and 7 even numbers)

enter image description here

This is the "skimmed" example of numbers I need to find all 8 odd and 7 pair combinations and the results: enter image description here

Maybe VBA is not the tool for that, maybe Python...any thoughts?

*Important: The position of the numbers does not matter (number 1 can be in column 1,A or in column 15,O)


Solution

  • this code worked:

    Sub GenerateCombinations() Dim nums As Variant Dim odds As Variant Dim evens As Variant Dim oddComb As Variant Dim evenComb As Variant Dim i As Long, j As Long, k As Long Dim ws As Worksheet Dim resultWs As Worksheet Dim oddCounter As Long Dim evenCounter As Long Dim combinationCounter As Long

    ' Read numbers from the sheet
    nums = Range("A1:A18").Value
    
    ' Separate odd and even numbers
    ReDim odds(1 To 10)
    ReDim evens(1 To 8)
    oddCounter = 1
    evenCounter = 1
    
    For i = 1 To UBound(nums)
        If nums(i, 1) Mod 2 = 0 Then
            evens(evenCounter) = nums(i, 1)
            evenCounter = evenCounter + 1
        Else
            odds(oddCounter) = nums(i, 1)
            oddCounter = oddCounter + 1
        End If
    Next i
    
    ' Generate combinations
    Set ws = ThisWorkbook.Sheets("Numbers")
    Set resultWs = ThisWorkbook.Sheets.Add
    resultWs.Name = "Combinations"
    combinationCounter = 1
    
    For i = 1 To UBound(odds) - 7
        For j = i + 1 To UBound(odds) - 6
            For k = j + 1 To UBound(odds) - 5
                For l = k + 1 To UBound(odds) - 4
                    For m = l + 1 To UBound(odds) - 3
                        For n = m + 1 To UBound(odds) - 2
                            For o = n + 1 To UBound(odds) - 1
                                For p = o + 1 To UBound(odds)
                                    For q = 1 To UBound(evens) - 6
                                        For r = q + 1 To UBound(evens) - 5
                                            For s = r + 1 To UBound(evens) - 4
                                                For t = s + 1 To UBound(evens) - 3
                                                    For u = t + 1 To UBound(evens) - 2
                                                        For v = u + 1 To UBound(evens) - 1
                                                            For w = v + 1 To UBound(evens)
                                                                resultWs.Cells(combinationCounter, 1).Value = odds(i)
                                                                resultWs.Cells(combinationCounter, 2).Value = odds(j)
                                                                resultWs.Cells(combinationCounter, 3).Value = odds(k)
                                                                resultWs.Cells(combinationCounter, 4).Value = odds(l)
                                                                resultWs.Cells(combinationCounter, 5).Value = odds(m)
                                                                resultWs.Cells(combinationCounter, 6).Value = odds(n)
                                                                resultWs.Cells(combinationCounter, 7).Value = odds(o)
                                                                resultWs.Cells(combinationCounter, 8).Value = odds(p)
                                                                resultWs.Cells(combinationCounter, 9).Value = evens(q)
                                                                resultWs.Cells(combinationCounter, 10).Value = evens(r)
                                                                resultWs.Cells(combinationCounter, 11).Value = evens(s)
                                                                resultWs.Cells(combinationCounter, 12).Value = evens(t)
                                                                resultWs.Cells(combinationCounter, 13).Value = evens(u)
                                                                resultWs.Cells(combinationCounter, 14).Value = evens(v)
                                                                resultWs.Cells(combinationCounter, 15).Value = evens(w)
                                                                combinationCounter = combinationCounter + 1
                                                            Next w
                                                        Next v
                                                    Next u
                                                Next t
                                            Next s
                                        Next r
                                    Next q
                                Next p
                            Next o
                        Next n
                    Next m
                Next l
            Next k
        Next j
    Next i
    
    MsgBox "Combinations generated successfully!"
    

    End Sub