Los Angeles United States
Member #75,408
June 2, 2009
590 Posts
Offline
Thank you Raven62,
I know I had it posted before but the answer was with a macro, and not a FORMULA, so if someone can help, it would be highly appreciated, I need a formula or formulas if posible.
Function Fact(N As Integer) As Double
If (N < 1) Then
Fact = 1
Else
Fact = N * Fact(N - 1)
End If
End Function
Function Perm(N As Integer, R As Integer) As Double
If (N < R) Then
Perm = 0
Else
Perm = Fact(N) / Fact(N - R)
End If
End Function
Function Comb(N As Integer, R As Integer) As Double
If (N < R) Then
Comb = 0
Else
Comb = Perm(N, R) / Fact(R)
End If
End Function
Function Cdist(N As Integer, R As Integer, C As Integer, Z As Integer) As Double
If (Z < C) Or (Z > (N - R + C)) Or (Z > N) Or (C > R) Or (N < 1) Or (R < 1) Or (C < 1) Or (Z < 1) Then
Cdist = 0
Else
Cdist = Comb(Z - 1, C - 1) * Comb(N - Z, R - C)
End If
End Function
Function fColumnSum(N As Integer, R As Integer, Z As Integer) As Double
Dim a As Integer
If Z < 1 Then
fColumnSum = 0
ElseIf (Z >= 1) And (Z < N - R + 1) Then
col_sum = 0
For a = 1 To Z
col_sum = col_sum + Cdist(N, R, 1, a)
Next a
fColumnSum = col_sum
ElseIf Z >= N - R + 1 Then
fColumnSum = Comb(N, R)
End If
End Function
Function Index2Combin(N As Integer, R As Integer, I As Double) As String
Dim Combination(), Z As Integer
ReDim Combination(R)
Dim tmpString As String
Dim NumberFound As Boolean
tmpString = ""
I = I - 1
Z = 0
For a = 1 To R
If I <= Comb(N, R) Then
If a = 1 Then
Combination(a) = 1
Else
Combination(a) = Combination(a - 1) + 1
End If
NumberFound = False
Do
Select Case (I - fColumnSum(N - Z, R - (a - 1), Combination(a) - Z - 1))
Case Is < 0
Combination(a) = Combination(a) - 1
NumberFound = True
Case Is = 0
NumberFound = True
Case Is > 0
Combination(a) = Combination(a) + 1
End Select
Loop Until NumberFound
I = I - fColumnSum(N - Z, R - (a - 1), Combination(a) - Z - 1)
Z = Combination(a)
Else
Combination(a) = 0
End If
tmpString = tmpString & Format(Combination(a), "00")
If a < R Then tmpString = tmpString & " "
Next a
Index2Combin = tmpString
End Function
Function Combin2Index(N As Integer, R As Integer, theRange As Range) As Double
Dim a As Integer
Dim fSum As Double
Dim NotInAscendingOrder, NotInPool As Boolean
NotInAscendingOrder = False
NotInPool = False
If (theRange.Rows.Count <> 1) Or (theRange.Columns.Count <> R) Then
Combin2Index = -1
Exit Function
End If
For a = 1 To R
If a < R Then
If theRange.Cells(1, a) >= theRange.Cells(1, a + 1) Then
NotInAscendingOrder = True
End If
End If
If (theRange.Cells(1, a) < 1) Or (theRange.Cells(1, a) > N) Then
NotInPool = True
End If
Next a
If NotInAscendingOrder Or NotInPool Then
Combin2Index = -1
Exit Function
End If
fSum = fColumnSum(N, R, theRange.Cells(1, 1) - 1) + 1
For a = 2 To R
fSum = fSum + fColumnSum(N - theRange.Cells(1, a - 1), R - a + 1, theRange.Cells(1, a) - theRange.Cells(1, a - 1) - 1)
Next a
Combin2Index = fSum
MN United States
Member #21
December 7, 2001
4,811 Posts
Offline
Corrected a Data Type glitch, but could have some limitations on very large Combinatorial Sets.
Play around with the Data Types on different programming platforms.
_________________________________________
Function Fact(N As Integer) As Double
If (N < 1) Then
Fact = 1
Else
Fact = N * Fact(N - 1)
End If
End Function
Function Perm(N As Integer, R As Integer) As Double
If (N < R) Then
Perm = 0
Else
Perm = Fact(N) / Fact(N - R)
End If
End Function
Function Comb(N As Integer, R As Integer) As Long
If (N < R) Then
Comb = 0
Else
Comb = Perm(N, R) / Fact(R)
End If
End Function
Function Cdist(N As Integer, R As Integer, C As Integer, Z As Integer) As Long
If (Z < C) Or (Z > (N - R + C)) Or (Z > N) Or (C > R) Or (N < 1) Or (R < 1) Or (C < 1) Or (Z < 1) Then
Cdist = 0
Else
Cdist = Comb(Z - 1, C - 1) * Comb(N - Z, R - C)
End If
End Function
Function fColumnSum(N As Integer, R As Integer, Z As Integer) As Long
Dim a As Integer
If Z < 1 Then
fColumnSum = 0
ElseIf (Z >= 1) And (Z < N - R + 1) Then
col_sum = 0
For a = 1 To Z
col_sum = col_sum + Cdist(N, R, 1, a)
Next a
fColumnSum = col_sum
ElseIf Z >= N - R + 1 Then
fColumnSum = Comb(N, R)
End If
End Function
Function Index2Combin(N As Integer, R As Integer, I As Double) As String
Dim Combination(), Z As Integer
ReDim Combination(R)
Dim tmpString As String
Dim NumberFound As Boolean
tmpString = ""
I = I - 1
Z = 0
For a = 1 To R
If I <= Comb(N, R) Then
If a = 1 Then
Combination(a) = 1
Else
Combination(a) = Combination(a - 1) + 1
End If
NumberFound = False
Do
Select Case (I - fColumnSum(N - Z, R - (a - 1), Combination(a) - Z - 1))
Case Is < 0
Combination(a) = Combination(a) - 1
NumberFound = True
Case Is = 0
NumberFound = True
Case Is > 0
Combination(a) = Combination(a) + 1
End Select
Loop Until NumberFound
I = I - fColumnSum(N - Z, R - (a - 1), Combination(a) - Z - 1)
Z = Combination(a)
Else
Combination(a) = 0
End If
tmpString = tmpString & Format(Combination(a), "00")
If a < R Then tmpString = tmpString & " "
Next a
Index2Combin = tmpString
End Function
Function Combin2Index(N As Integer, R As Integer, theRange As Range) As Long
Dim a As Integer
Dim fSum As Double
Dim NotInAscendingOrder, NotInPool As Boolean
NotInAscendingOrder = False
NotInPool = False
If (theRange.Rows.Count <> 1) Or (theRange.Columns.Count <> R) Then
Combin2Index = -1
Exit Function
End If
For a = 1 To R
If a < R Then
If theRange.Cells(1, a) >= theRange.Cells(1, a + 1) Then
NotInAscendingOrder = True
End If
End If
If (theRange.Cells(1, a) < 1) Or (theRange.Cells(1, a) > N) Then
NotInPool = True
End If
Next a
If NotInAscendingOrder Or NotInPool Then
Combin2Index = -1
Exit Function
End If
fSum = fColumnSum(N, R, theRange.Cells(1, 1) - 1) + 1
For a = 2 To R
fSum = fSum + fColumnSum(N - theRange.Cells(1, a - 1), R - a + 1, theRange.Cells(1, a) - theRange.Cells(1, a - 1) - 1)
Next a
Combin2Index = fSum
Los Angeles United States
Member #75,408
June 2, 2009
590 Posts
Offline
Thank you very much JADE, for your extensive work ,I'm impressed.
But one question : this is the second time I'm asking for a Excel formula, and both time I've got answered with a macro solution, is that mean that it's not posible to get the result with a formula ?
For the time been, I will change my file and use your macro instead.
New Jersey United States
Member #17,842
June 28, 2005
180,983 Posts
Offline
Quote: Originally posted by frenchie on Sep 1, 2010
Thank you Raven62,
I know I had it posted before but the answer was with a macro, and not a FORMULA, so if someone can help, it would be highly appreciated, I need a formula or formulas if posible.
MN United States
Member #21
December 7, 2001
4,811 Posts
Offline
Quote: Originally posted by JADELottery on Sep 4, 2010
Corrected a Data Type glitch, but could have some limitations on very large Combinatorial Sets.
Play around with the Data Types on different programming platforms.
_________________________________________
Function Fact(N As Integer) As Double
If (N < 1) Then
Fact = 1
Else
Fact = N * Fact(N - 1)
End If
End Function
Function Perm(N As Integer, R As Integer) As Double
If (N < R) Then
Perm = 0
Else
Perm = Fact(N) / Fact(N - R)
End If
End Function
Function Comb(N As Integer, R As Integer) As Long
If (N < R) Then
Comb = 0
Else
Comb = Perm(N, R) / Fact(R)
End If
End Function
Function Cdist(N As Integer, R As Integer, C As Integer, Z As Integer) As Long
If (Z < C) Or (Z > (N - R + C)) Or (Z > N) Or (C > R) Or (N < 1) Or (R < 1) Or (C < 1) Or (Z < 1) Then
Cdist = 0
Else
Cdist = Comb(Z - 1, C - 1) * Comb(N - Z, R - C)
End If
End Function
Function fColumnSum(N As Integer, R As Integer, Z As Integer) As Long
Dim a As Integer
If Z < 1 Then
fColumnSum = 0
ElseIf (Z >= 1) And (Z < N - R + 1) Then
col_sum = 0
For a = 1 To Z
col_sum = col_sum + Cdist(N, R, 1, a)
Next a
fColumnSum = col_sum
ElseIf Z >= N - R + 1 Then
fColumnSum = Comb(N, R)
End If
End Function
Function Index2Combin(N As Integer, R As Integer, I As Double) As String
Dim Combination(), Z As Integer
ReDim Combination(R)
Dim tmpString As String
Dim NumberFound As Boolean
tmpString = ""
I = I - 1
Z = 0
For a = 1 To R
If I <= Comb(N, R) Then
If a = 1 Then
Combination(a) = 1
Else
Combination(a) = Combination(a - 1) + 1
End If
NumberFound = False
Do
Select Case (I - fColumnSum(N - Z, R - (a - 1), Combination(a) - Z - 1))
Case Is < 0
Combination(a) = Combination(a) - 1
NumberFound = True
Case Is = 0
NumberFound = True
Case Is > 0
Combination(a) = Combination(a) + 1
End Select
Loop Until NumberFound
I = I - fColumnSum(N - Z, R - (a - 1), Combination(a) - Z - 1)
Z = Combination(a)
Else
Combination(a) = 0
End If
tmpString = tmpString & Format(Combination(a), "00")
If a < R Then tmpString = tmpString & " "
Next a
Index2Combin = tmpString
End Function
Function Combin2Index(N As Integer, R As Integer, theRange As Range) As Long
Dim a As Integer
Dim fSum As Double
Dim NotInAscendingOrder, NotInPool As Boolean
NotInAscendingOrder = False
NotInPool = False
If (theRange.Rows.Count <> 1) Or (theRange.Columns.Count <> R) Then
Combin2Index = -1
Exit Function
End If
For a = 1 To R
If a < R Then
If theRange.Cells(1, a) >= theRange.Cells(1, a + 1) Then
NotInAscendingOrder = True
End If
End If
If (theRange.Cells(1, a) < 1) Or (theRange.Cells(1, a) > N) Then
NotInPool = True
End If
Next a
If NotInAscendingOrder Or NotInPool Then
Combin2Index = -1
Exit Function
End If
fSum = fColumnSum(N, R, theRange.Cells(1, 1) - 1) + 1
For a = 2 To R
fSum = fSum + fColumnSum(N - theRange.Cells(1, a - 1), R - a + 1, theRange.Cells(1, a) - theRange.Cells(1, a - 1) - 1)
Next a
Combin2Index = fSum
End Function
FYI, the Data Types are the Double, Long, Integer, and String parts of the code.
the Excel file is setup and ready to be manipulated as you see fit, however you need to enable macros when using it.
if you don't want to download the file, then copy and paste the posted code here into your VB macro editor.
we recommend looking over the Excel file though, because the file has some functions that can break up the returned string value in the =Index2Combin(N, R, Index) function.
Step #1 - I added the highest value or the maximum number of balls drawn. For example I entered a 49 in cell L1
Step #2 - I added the highest value for the total numal of balls drawn in cell L2. For example I entered a 6 in cell L2
In cell L3 there is a formula that calculates the highest Combinatorial Index Number as JadeLottery calls it or Lexicogrphic Number or Combinational Sequence Number or whatever name you want to call it.
In Step # 3 - You just need to enter the Combinatorial Index Number, Lexicogrphic Number, Combinational Sequence Number etc. The value must be any from 1 to the highest value in cell L3.
Frenchie all you need to do is just enter your lottery's values in cells L1 and L2 and then whatever value (Lexi, CSN, etc.) starting in cell A10. You can drag all the formulas down.
You can change the value in Cell L1 to a 39 and change the value in cell L2 to a 5 for a 5/39 Matrix Lottery game then enter your values starting in cell A10.
Function Fact(N As Integer) As Double
If (N < 1) Then
Fact = 1
Else
Fact = N * Fact(N - 1)
End If
End Function
Function Perm(N As Integer, R As Integer) As Double
If (N < R) Then
Perm = 0
Else
Perm = Fact(N) / Fact(N - R)
End If
End Function
Function Comb(N As Integer, R As Integer) As Double
If (N < R) Then
Comb = 0
Else
Comb = Perm(N, R) / Fact(R)
End If
End Function
Function Cdist(N As Integer, R As Integer, C As Integer, Z As Integer) As Double
If (Z < C) Or (Z > (N - R + C)) Or (Z > N) Or (C > R) Or (N < 1) Or (R < 1) Or (C < 1) Or (Z < 1) Then
Cdist = 0
Else
Cdist = Comb(Z - 1, C - 1) * Comb(N - Z, R - C)
End If
End Function
Function fColumnSum(N As Integer, R As Integer, Z As Integer) As Double
Dim a As Integer
If Z < 1 Then
fColumnSum = 0
ElseIf (Z >= 1) And (Z < N - R + 1) Then
col_sum = 0
For a = 1 To Z
col_sum = col_sum + Cdist(N, R, 1, a)
Next a
fColumnSum = col_sum
ElseIf Z >= N - R + 1 Then
fColumnSum = Comb(N, R)
End If
End Function
Function Index2Combin(N As Integer, R As Integer, I As Double) As String
Dim Combination(), Z As Integer
ReDim Combination(R)
Dim tmpString As String
Dim NumberFound As Boolean
tmpString = ""
I = I - 1
Z = 0
For a = 1 To R
If I <= Comb(N, R) Then
If a = 1 Then
Combination(a) = 1
Else
Combination(a) = Combination(a - 1) + 1
End If
NumberFound = False
Do
Select Case (I - fColumnSum(N - Z, R - (a - 1), Combination(a) - Z - 1))
Case Is < 0
Combination(a) = Combination(a) - 1
NumberFound = True
Case Is = 0
NumberFound = True
Case Is > 0
Combination(a) = Combination(a) + 1
End Select
Loop Until NumberFound
I = I - fColumnSum(N - Z, R - (a - 1), Combination(a) - Z - 1)
Z = Combination(a)
Else
Combination(a) = 0
End If
tmpString = tmpString & Format(Combination(a), "00")
If a < R Then tmpString = tmpString & " "
Next a
Index2Combin = tmpString
End Function
Function Combin2Index(N As Integer, R As Integer, theRange As Range) As Double
Dim a As Integer
Dim fSum As Double
Dim NotInAscendingOrder, NotInPool As Boolean
NotInAscendingOrder = False
NotInPool = False
If (theRange.Rows.Count <> 1) Or (theRange.Columns.Count <> R) Then
Combin2Index = -1
Exit Function
End If
For a = 1 To R
If a < R Then
If theRange.Cells(1, a) >= theRange.Cells(1, a + 1) Then
NotInAscendingOrder = True
End If
End If
If (theRange.Cells(1, a) < 1) Or (theRange.Cells(1, a) > N) Then
NotInPool = True
End If
Next a
If NotInAscendingOrder Or NotInPool Then
Combin2Index = -1
Exit Function
End If
fSum = fColumnSum(N, R, theRange.Cells(1, 1) - 1) + 1
For a = 2 To R
fSum = fSum + fColumnSum(N - theRange.Cells(1, a - 1), R - a + 1, theRange.Cells(1, a) - theRange.Cells(1, a - 1) - 1)
Next a
Combin2Index = fSum
End Function
We've made one more tweak to the code.
This should get larger combination sets, but be careful.
We're not sure how well it works with larger sets beyond about Pick 10; maybe less.
If you would like just the code, it's posted below.
The VB Macro Source Code
Function Fact(N As Integer) As Double
If (N <= 1) Then
Fact = 1
Else
Fact = N * Fact(N - 1)
End If
End Function
Function Perm(N As Integer, R As Integer) As Double
Dim a As Integer
Dim b As Double
b = 1
If (N < R) Then
Perm = 0
Else
For a = N - R + 1 To N
b = b * a
Next a
Perm = b
End If
End Function
Function Comb(N As Integer, R As Integer) As Double
If (N < R) Then
Comb = 0
Else
Comb = Perm(N, R) / Fact(R)
End If
End Function
Function Cdist(N As Integer, R As Integer, C As Integer, Z As Integer) As Double
If (Z < C) Or (Z > (N - R + C)) Or (Z > N) Or (C > R) Or (N < 1) Or (R < 1) Or (C < 1) Or (Z < 1) Then
Cdist = 0
Else
Cdist = Comb(Z - 1, C - 1) * Comb(N - Z, R - C)
End If
End Function
Function fColumnSum(N As Integer, R As Integer, Z As Integer) As Double
Dim a As Integer
If Z < 1 Then
fColumnSum = 0
ElseIf (Z >= 1) And (Z < N - R + 1) Then
col_sum = 0
For a = 1 To Z
col_sum = col_sum + Cdist(N, R, 1, a)
Next a
fColumnSum = col_sum
ElseIf Z >= N - R + 1 Then
fColumnSum = Comb(N, R)
End If
End Function
Function Index2Combin(N As Integer, R As Integer, I As Double) As String
Dim a, Combination(), Z As Integer
ReDim Combination(R)
Dim tmpString As String
Dim NumberFound As Boolean
tmpString = ""
I = I - 1
Z = 0
For a = 1 To R
If I <= (Comb(N, R) - 1) Then
If a = 1 Then
Combination(a) = 1
Else
Combination(a) = Combination(a - 1) + 1
End If
NumberFound = False
Do
Select Case (I - fColumnSum(N - Z, R - (a - 1), Combination(a) - Z - 1))
Case Is < 0
Combination(a) = Combination(a) - 1
NumberFound = True
Case Is = 0
NumberFound = True
Case Is > 0
Combination(a) = Combination(a) + 1
End Select
Loop Until NumberFound
I = I - fColumnSum(N - Z, R - (a - 1), Combination(a) - Z - 1)
Z = Combination(a)
Else
Combination(a) = 0
End If
tmpString = tmpString & Format(Combination(a), "00")
If a < R Then tmpString = tmpString & " "
Next a
Index2Combin = tmpString
End Function
Function Combin2Index(N As Integer, R As Integer, theRange As Range) As Double
Dim a As Integer
Dim fSum As Double
Dim NotInAscendingOrder, NotInPool As Boolean
NotInAscendingOrder = False
NotInPool = False
If (theRange.Rows.Count <> 1) Or (theRange.Columns.Count <> R) Then
Combin2Index = -1
Exit Function
End If
For a = 1 To R
If a < R Then
If theRange.Cells(1, a) >= theRange.Cells(1, a + 1) Then
NotInAscendingOrder = True
End If
End If
If (theRange.Cells(1, a) < 1) Or (theRange.Cells(1, a) > N) Then
NotInPool = True
End If
Next a
If NotInAscendingOrder Or NotInPool Then
Combin2Index = -1
Exit Function
End If
fSum = fColumnSum(N, R, theRange.Cells(1, 1) - 1) + 1
For a = 2 To R
fSum = fSum + fColumnSum(N - theRange.Cells(1, a - 1), R - a + 1, theRange.Cells(1, a) - theRange.Cells(1, a - 1) - 1)
Next a
Combin2Index = fSum