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