Help with a Formula Within a Function Please

[Paul Black]

Hi Everybody,

Can Somebody Please Help me with a Function.
I have a Macro that Produces 6 Number Combinations.
The First Number is Associated with Integer "A", the Second with
Integer "B" and so on.
I would like to be Able to Put a Range ( Using an "IF" Statement ) of
Lexicographic ( Combination 1 Being 01-02-03-04-05-06 ) Sequence
Numbers, so that a Combination Within that Range will be Accepted, and
a Combination Outside that Range will be Ignored.

The Excel Formula ( For a Combination of 6 Numbers in Cells O14:T14 )
is :-
=COMBIN(49,6)-IF(44-O14>0,COMBIN(49-O14,6),0)-IF(45-P14>0,COMBIN(49-P14,5),0)-IF(46-Q14>0,COMBIN(49-Q14,4),0)-IF(47-R14>0,COMBIN(49-R14,3),0)-IF(48-S14>0,COMBIN(49-S14,2),0)-IF(49-T14>0,COMBIN(49-T14,1),0)

Something Like ( as a Sequential Call ) :-
If LexNumber > 22500 And LexNumber < 50000 Then

Function :-
Function LexNumber()
LexNumber = False
*Code Will Go Here* Then
LexNumber = True
End Function

Any Help would be Appreciated
All the Best
Paul

 

[Tom Ogilvy]

Function LexNumber()
LexNumber = False
a = IIf(44 - Range("O14").Value > 0, _
Application.Combin(49 - _
Range("O14").Value, 6), 0)
b = IIf(45 - Range("P14").Value > 0, _
Application.Combin(49 - _
Range("P14").Value, 5), 0)
c = IIf(46 - Range("Q14").Value > 0, _
Application.Combin(49 - _
Range("Q14").Value, 4), 0)
d = IIf(47 - Range("R14").Value > 0, _
Application.Combin(49 - _
Range("R14").Value, 3), 0)
e = IIf(48 - Range("S14").Value > 0, _
Application.Combin(49 - _
Range("S14").Value, 2), 0)
f = IIf(49 - Range("T14").Value > 0, _
Application.Combin(49 - _
Range("T14").Value, 1), 0)
lNumber = Application.Combin(49, 6) _
- a - b - c - d - e - f
If lNumber > 22500 And lNumber < 50000 Then
LexNumber = True
End if
End Function

 

[Paul Black]

Thanks for the Reply Tom,

What I Ideally want is to Include the Function in the Follwing Macro,
so that as it Runs through the Combinations it Checks if the
Lexicographic Number of that Combination is within the Range Set. The
Formula I gave was to Show How it is Calculated in an Excel Formula.
The Macro Below Produces Combinations and then Writes them to a
Worksheet, I Only want Combinations Produced if the Lexicographic
Number is Within the Range Set Please.

Option Explicit
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As
Integer, F As Integer
Dim N As Long
Sub Test()
Range("A1").Select
Application.ScreenUpdating = False
N = 0
For A = 1 To 7
For B = A + 1 To 8
For C = B + 1 To 9
For D = C + 1 To 10
For E = D + 1 To 11
For F = E + 1 To 12
N = N + 1
ActiveCell.Value = A & "-" & B & "-" & C & "-" & D & "-" & E & "-" & F
ActiveCell.Offset(1, 0).Select
Next F
Next E
Next D
Next C
Next B
Next A
Application.ScreenUpdating = True
End Sub

All the Best
Paul

 

[Dana DeLouis]

Just a general idea. In certain math programs, the function that you
supplied is called the "Rank of a KSubset." If I apply "Unrank KSubset" on
the two numbers you supplied (22500, 50000), I get...
1,2,4,14,24,25
&
1,2,6,18,19,23

(I may be off by one because I'm 0-based, and you want 1-based).
This would be your range of combinations.
I would work on an UnRank function. For example, if you only wanted 2-4
from your Subsets below, your code would still check all 924 combinations
(=COMBIN(12,6))
I would unrank the number 2 to get 1-2-3-4-5-7, and then loop only 3 times
(4-2+1) in your code below.
I'm not sure how to translate this function just yet, but maybe this might
give you an idea.

Here was my attempt at a "LexNumber" worksheet function which is similar to
Tom's.

=LexNumber(O14:T14,22500,50000)

Function LexNumber(v As Variant, L, H) As Boolean
'// Rank (only good for 6 of 49)
Dim T As Long
Dim j As Long

T = 0
With WorksheetFunction
For j = 1 To 6
If v(1, j) < (j + 43) Then T = T + (.Combin(49 - v(1, j), 7 -
j))
Next j
T = .Combin(49, 6) - T
End With
LexNumber = L < T And T < H
End Function

 

[Paul Black]

Thanks Dana,

I Tried your Code Using the Call :-

If LexNumber >= 10 And LexNumber <= 50 Then

I then Attached your Function Code at the Bottom of the Macro After
the End Sub ( Along with Several Other Functions I Have ).
I Played Around with it But Unfortunately could NOT get it to Work.
Do you have Any Ideas of what I am Doing Wrong Please.

All the Best
Paul

 

[Dana DeLouis]

Hi. I am not sure what you have set up. The problem is most likely in the
indexes of the variables that are passed to the function.
As a worksheet function, passing the range to the function produced indexes
of (1,1), (1,2)...(1,6). I used an array variable v as in " v(1, j) ".
If you are calling the function from a Macro, you would want to make sure
your indexes are set up the way you want them. For example, if your main
macro has 6 variables, plus a High & Low variable, and you wanted to pass
these to a function, you could pass them as
Check = LexNumber(a,b,c,d,e,f,H,L). However, using variables like this
would not work well in a loop as written.

Another option...
Check = LexNumber(Array(a,b,c,d,e,f,H,L))

and just realize that in your function,

Function LexNumber(v As Variant) As Boolean

that the variable v is a 0 (zero) based array, and the macro function would
have to be adjusted for this.

I'm having a hard time with the Unrank idea. It should be simple. It uses
the same idea you had using Combin (x,y) in a loop.
The idea is you start with 10 (being {1, 2, 3, 4, 6, 9}, and then only loop
40 times in your code to get to 50.