Welcome Guest
You last visited January 24, 2017, 2:35 am
All times shown are
Eastern Time (GMT-5:00)

# Excel help need Combination FROM Lexicographix number

Topic closed. 26 replies. Last post 6 years ago by RL-RANDOMLOGIC.

 Page 2 of 2
The Quantum Master
West Concord, MN
United States
Member #21
December 7, 2001
3685 Posts
Offline
 Posted: September 6, 2010, 11:29 am - IP Logged

We're in the process of making one more tweak.

It has to do with the String that is returned and the value of N going in.

It's more of a formatting issue.

When the value of N is greater than 99, the String will display something like, (... 89 90 99 106 ...)

We are reformatting it to display with leading 0's like this (... 089 090 099 106 ...)

This will make it easier when Separating the Combination in to individual values.

So if you want to wait a bit, we'll have it out later.

Presented 'AS IS' and for Entertainment Purposes Only.
Any gain or loss is your responsibility.

Order is a Subset of Chaos
Knowledge is Beyond Belief
Wisdom is Not Censored
Douglas Paul Smallish
Jehocifer

The Quantum Master
West Concord, MN
United States
Member #21
December 7, 2001
3685 Posts
Offline
 Posted: September 6, 2010, 12:39 pm - IP Logged

Ok, it's out there.

There are also a few tweaks in the workbook itself.

Enjoy.

or

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, b, Combination(), Z As Integer
ReDim Combination(R)
Dim tmpString, CombinFormat As String
Dim NumberFound As Boolean

tmpString = ""
CombinFormat = ""
I = I - 1
Z = 0
b = Len(Format(N, "0"))

For a = 1 To b
CombinFormat = CombinFormat & "0"
Next a

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), CombinFormat)
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 = 1
For a = 1 To R
If a = 1 Then
fSum = fSum + fColumnSum(N, R, theRange.Cells(1, 1) - 1)
Else
fSum = fSum + fColumnSum(N - theRange.Cells(1, a - 1), R - a + 1, theRange.Cells(1, a) - theRange.Cells(1, a - 1) - 1)
End If
Next a

Combin2Index = fSum

End Function

Presented 'AS IS' and for Entertainment Purposes Only.
Any gain or loss is your responsibility.

Order is a Subset of Chaos
Knowledge is Beyond Belief
Wisdom is Not Censored
Douglas Paul Smallish
Jehocifer

The Quantum Master
West Concord, MN
United States
Member #21
December 7, 2001
3685 Posts
Offline
 Posted: September 6, 2010, 1:15 pm - IP Logged

Those of you with 64-bit machines may have a better chance of handling larger Pool and Pick sizes.

It also may be dependent on the Excel version or Language platform.

For the most part, if you keep the values to a Pool Size of (1 - 99) and Pick Size (1 - 10), you should do ok.

Presented 'AS IS' and for Entertainment Purposes Only.
Any gain or loss is your responsibility.

Order is a Subset of Chaos
Knowledge is Beyond Belief
Wisdom is Not Censored
Douglas Paul Smallish
Jehocifer

The Quantum Master
West Concord, MN
United States
Member #21
December 7, 2001
3685 Posts
Offline
 Posted: September 6, 2010, 4:40 pm - IP Logged

We've added some extra stuff and examples in the ' Function Information ' sheet (bottom tab).

or

Presented 'AS IS' and for Entertainment Purposes Only.
Any gain or loss is your responsibility.

Order is a Subset of Chaos
Knowledge is Beyond Belief
Wisdom is Not Censored
Douglas Paul Smallish
Jehocifer

The Quantum Master
West Concord, MN
United States
Member #21
December 7, 2001
3685 Posts
Offline
 Posted: September 7, 2010, 2:33 am - IP Logged

There have been some changes, but it looks like if you download with Internet Explorer the file may not reload correctly.

You might have to clear out the IE Browser Cache or Use Mozilla Firefox, it downloads the updated file correctly each time.

It's another one of those Microsoft things.

Presented 'AS IS' and for Entertainment Purposes Only.
Any gain or loss is your responsibility.

Order is a Subset of Chaos
Knowledge is Beyond Belief
Wisdom is Not Censored
Douglas Paul Smallish
Jehocifer

New Member
Nairobi
Kenya
Member #93075
June 21, 2010
19 Posts
Offline
 Posted: September 7, 2010, 4:24 am - IP Logged

I have looked at you codes and I must appreciate the great work and thinking involved.

I was wondering why you have to create comb function which calls perm and fact functions instead of using Excel inbuilt combin function i.e application.combin(Z,R)

When dealing with large array which is faster between your function (comb(Z,R)) and Excel inbuilt function (Application.Combin(Z,R))?

Thanks and keep the good work

Kenya649

The Quantum Master
West Concord, MN
United States
Member #21
December 7, 2001
3685 Posts
Offline
 Posted: September 7, 2010, 9:46 am - IP Logged

I have looked at you codes and I must appreciate the great work and thinking involved.

I was wondering why you have to create comb function which calls perm and fact functions instead of using Excel inbuilt combin function i.e application.combin(Z,R)

When dealing with large array which is faster between your function (comb(Z,R)) and Excel inbuilt function (Application.Combin(Z,R))?

Thanks and keep the good work

Kenya649

If it's faster in your tests, use it.

We did the code this way because the application.combin() is not available in a programming platform like Visual Studio - Visual Basic.

You can for the most part, copy and paste the code directly into VS-VB and it should work.

The exception being the Combin2Index() function; there you would have to transform the ' theRange As Range' into an array ' theRange() As Integer '.

Presented 'AS IS' and for Entertainment Purposes Only.
Any gain or loss is your responsibility.

Order is a Subset of Chaos
Knowledge is Beyond Belief
Wisdom is Not Censored
Douglas Paul Smallish
Jehocifer

The Quantum Master
West Concord, MN
United States
Member #21
December 7, 2001
3685 Posts
Offline
 Posted: September 7, 2010, 10:16 am - IP Logged

Ok, this should be that last update for this file.

Below the links, we are also posting the Excel - Visual Basic Code and the Visual Studio - Visual Basic Code.

Each has been formatted for their respective platforms.

or

Excel - Visual Basic Code

Function RandomLowerUpper(ByVal L As Double, ByVal U As Double) As Double
Randomize Timer
RandomLowerUpper = Int(Rnd() * (U - L + 1)) + L
End Function

Function Fact(ByVal N As Integer) As Double
If (N <= 1) Then
Fact = 1
Else
Fact = N * Fact(N - 1)
End If
End Function

Function Perm(ByVal N As Integer, ByVal 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(ByVal N As Integer, ByVal R As Integer) As Double
If (N < R) Then
Comb = 0
Else
Comb = Perm(N, R) / Fact(R)
End If
End Function

Function Cdist(ByVal N As Integer, ByVal R As Integer, ByVal C As Integer, ByVal 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(ByVal N As Integer, ByVal R As Integer, ByVal Z As Integer) As Double
Dim a As Integer
Dim ColumnSum As Double
If Z < 1 Then
fColumnSum = 0
ElseIf (Z >= 1) And (Z < N - R + 1) Then
ColumnSum = 0
For a = 1 To Z
ColumnSum = ColumnSum + Cdist(N, R, 1, a)
Next a
fColumnSum = ColumnSum
ElseIf Z >= N - R + 1 Then
fColumnSum = Comb(N, R)
End If
End Function

Function Index2Combin(ByVal N As Integer, ByVal R As Integer, ByVal I As Double) As String
Dim a, b, Combination(), Z As Integer
Dim J As Double
ReDim Combination(R)
Dim tmpString, CombinFormat As String
Dim NumberFound As Boolean
tmpString = ""
CombinFormat = ""
J = I
J = J - 1
Z = 0
b = Len(Format(N, "0"))
For a = 1 To b
CombinFormat = CombinFormat & "0"
Next a
For a = 1 To R
If (I >= 1) And (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 (J - 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
J = J - fColumnSum(N - Z, R - (a - 1), Combination(a) - Z - 1)
Z = Combination(a)
Else
Combination(a) = 0
End If
tmpString = tmpString & Format(Combination(a), CombinFormat)
If a < R Then tmpString = tmpString & " "
Next a
Index2Combin = tmpString
End Function

Function Combin2Index(ByVal N As Integer, ByVal R As Integer, ByVal 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 = 1
For a = 1 To R
If a = 1 Then
fSum = fSum + fColumnSum(N, R, theRange.Cells(1, 1) - 1)
Else
fSum = fSum + fColumnSum(N - theRange.Cells(1, a - 1), R - a + 1, theRange.Cells(1, a) - theRange.Cells(1, a - 1) - 1)
End If
Next a
Combin2Index = fSum
End Function

Visual Studio - Visual Basic Code

Function RandomLowerUpper(ByVal L As Double, ByVal U As Double) As Double
Randomize()
RandomLowerUpper = Int(Rnd() * (U - L + 1)) + L
End Function

Function Fact(ByVal N As Integer) As Double
If (N <= 1) Then
Fact = 1
Else
Fact = N * Fact(N - 1)
End If
End Function

Function Perm(ByVal N As Integer, ByVal 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(ByVal N As Integer, ByVal R As Integer) As Double
If (N < R) Then
Comb = 0
Else
Comb = Perm(N, R) / Fact(R)
End If
End Function

Function Cdist(ByVal N As Integer, ByVal R As Integer, ByVal C As Integer, ByVal 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(ByVal N As Integer, ByVal R As Integer, ByVal Z As Integer) As Double
Dim a As Integer
Dim ColumnSum As Double
If Z < 1 Then
fColumnSum = 0
ElseIf (Z >= 1) And (Z < N - R + 1) Then
ColumnSum = 0
For a = 1 To Z
ColumnSum = ColumnSum + Cdist(N, R, 1, a)
Next a
fColumnSum = ColumnSum
ElseIf Z >= N - R + 1 Then
fColumnSum = Comb(N, R)
End If
End Function

Function Index2Combin(ByVal N As Integer, ByVal R As Integer, ByVal I As Double) As String
Dim a, b, Combination(), Z As Integer
Dim J As Double
ReDim Combination(R)
Dim tmpString, CombinFormat As String
Dim NumberFound As Boolean
tmpString = ""
CombinFormat = ""
J = I
J = J - 1
Z = 0
b = Len(Format(N, "0"))
For a = 1 To b
CombinFormat = CombinFormat & "0"
Next a
For a = 1 To R
If (I >= 1) And (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 (J - 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
J = J - fColumnSum(N - Z, R - (a - 1), Combination(a) - Z - 1)
Z = Combination(a)
Else
Combination(a) = 0
End If
tmpString = tmpString & Format(Combination(a), CombinFormat)
If a < R Then tmpString = tmpString & " "
Next a
Index2Combin = tmpString
End Function

Function Combin2Index(ByVal N As Integer, ByVal R As Integer, ByVal theRange() As Integer) As Double
Dim a As Integer
Dim fSum As Double
Dim NotInAscendingOrder, NotInPool As Boolean
NotInAscendingOrder = False
NotInPool = False
If (UBound(theRange) <> R) Then
Combin2Index = -1
Exit Function
End If
For a = 1 To R
If a < R Then
If theRange(a) >= theRange(a + 1) Then
NotInAscendingOrder = True
End If
End If
If (theRange(a) < 1) Or (theRange(a) > N) Then
NotInPool = True
End If
Next a
If NotInAscendingOrder Or NotInPool Then
Combin2Index = -1
Exit Function
End If
fSum = 1
For a = 1 To R
If a = 1 Then
fSum = fSum + fColumnSum(N, R, theRange(1) - 1)
Else
fSum = fSum + fColumnSum(N - theRange(a - 1), R - a + 1, theRange(a) - theRange(a - 1) - 1)
End If
Next a
Combin2Index = fSum
End Function

Presented 'AS IS' and for Entertainment Purposes Only.
Any gain or loss is your responsibility.

Order is a Subset of Chaos
Knowledge is Beyond Belief
Wisdom is Not Censored
Douglas Paul Smallish
Jehocifer

New Member
Nairobi
Kenya
Member #93075
June 21, 2010
19 Posts
Offline
 Posted: September 8, 2010, 2:49 am - IP Logged

keep it up

Thanks

Netherlands
Member #3476
January 24, 2004
212 Posts
Offline
 Posted: September 8, 2010, 7:12 am - IP Logged

Hi,

For everybody interested in the original VB code it can be found here:  http://www.xtremevbtalk.com/archive/index.php/t-168296.html

I converted the code to vb.net  and use it for my own projects.

Cheers

The Quantum Master
West Concord, MN
United States
Member #21
December 7, 2001
3685 Posts
Offline
 Posted: September 8, 2010, 12:13 pm - IP Logged

Hi,

For everybody interested in the original VB code it can be found here:  http://www.xtremevbtalk.com/archive/index.php/t-168296.html

I converted the code to vb.net  and use it for my own projects.

Cheers

Nifty page, bookmarked that one.

Thanks.

Presented 'AS IS' and for Entertainment Purposes Only.
Any gain or loss is your responsibility.

Order is a Subset of Chaos
Knowledge is Beyond Belief
Wisdom is Not Censored
Douglas Paul Smallish
Jehocifer

United States
Member #59354
March 13, 2008
4094 Posts
Offline
 Posted: September 15, 2010, 8:24 am - IP Logged
` If anyone is interested in getting the set from the lexigraph number, Here is a way to  calculate it using a calculator.     first calculate the highest lexi for the matrix  example 5-39  39C5 = 575757  NOW SAY YOUR LOOKING FOR THE SET FROM LEXI VALUE  120,089  SUBTRACT 575757 - 120089 = 455668  NOW FIND THE FIRST VALUE THAT IS LESS THEN OR EQUAL TO 455668  39C5 = 575757  38C5 = 501942  37C5 = 435897  THIS VALUE IS LESS THEN OR EQUAL TO 455668 SO 39-37 = 2, SO A1 =2  NOW SUBTRACT 455668 - 435897 = 19771  NEXT SUBTRACT ONE BALL 5-1 = 4 AND CONTINUE  37C4 = 66045  36C4 = 58905  35C4 = 52360  34C4 = 46376  33C4 = 40920  32C4 = 35960  31C4 = 31465  30C4 = 27405  29C4 = 23751  28C4 = 20475  27C4 = 17550  THIS VALUE IS LESS THEN OR EQUAL TO 19771 SO (37 - (27-A1)) = 12, SO B1 = 12  NOW SUBTRACT 19771 - 17550  = 2221  NEXT SUBTRACT ONE BALL 4-1 = 3 AND CONTINUE  27C3 = 2925  26C3 = 2600  25C3 = 2300  24C3 = 2024  THIS VALUE IS LESS THEN OR EQUAL TO 2221 SO (27 - (24-B1)) = 15, SO C1 = 15  NOW SUBTRACT 2221 - 2024 = 197  NEXT SUBTRACT ONE BALL 3-1 = 2 AND CONTINUE  24C2 = 276  23C2 = 253  22C2 = 231  21C2 = 210  20C2 = 190  THIS VALUE IS LESS THEN OR EQUAL TO 197 SO (24 - (20-C1)) = 19, SO D1 = 19  NOW SUBTRACT 197 - 190 = 7  FINAL STEP SUBTRACT 39-7 = 32 SO E1 = 32  A1 = 02  B1 = 12  C1 = 15  D1 = 19  E1 = 32  IF MAKING A 6 NUMBER CONVERSION THEN ADD ANOTHER STEP BETWEEN D1 AND E1 AND RENAME E1 TO F1  RL`

Working on my Ph.D.  "University of hard Knocks"

I will consider the opinion that my winnings are a product of chance if you are willing to consider

they are not.  Many great discoveries come while searching for something else

Trump / 2016 & 2020

 Page 2 of 2