Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with a Formula Within a Function Please
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-O140,COMBIN(49-O14,6),0)-IF(45-P140,COMBIN(49-P14,5),0)-IF(46-Q140,COMBIN(49-Q14,4),0)-IF(47-R140,COMBIN(49-R14,3),0)-IF(48-S140,COMBIN(49-S14,2),0)-IF(49-T140,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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with a Formula Within a Function Please
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 -- Regards, Tom Ogilvy "Paul Black" wrote in message ... 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-O140,COMBIN(49-O14,6),0)-IF(45-P140,COMBIN(49-P14,5),0 )-IF(46-Q140,COMBIN(49-Q14,4),0)-IF(47-R140,COMBIN(49-R14,3),0)-IF(48-S14 0,COMBIN(49-S14,2),0)-IF(49-T140,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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with a Formula Within a Function Please
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 "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Paul Black" wrote in message ... 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-O140,COMBIN(49-O14,6),0)-IF(45-P140,COMBIN(49-P14,5),0 )-IF(46-Q140,COMBIN(49-Q14,4),0)-IF(47-R140,COMBIN(49-R14,3),0)-IF(48-S14 0,COMBIN(49-S14,2),0)-IF(49-T140,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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with a Formula Within a Function Please
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 -- Dana DeLouis Win XP & Office 2003 "Paul Black" wrote in message ... 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 "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Paul Black" wrote in message ... 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-O140,COMBIN(49-O14,6),0)-IF(45-P140,COMBIN(49-P14,5),0 )-IF(46-Q140,COMBIN(49-Q14,4),0)-IF(47-R140,COMBIN(49-R14,3),0)-IF(48-S14 0,COMBIN(49-S14,2),0)-IF(49-T140,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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with a Formula Within a Function Please
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" wrote in message ... 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 -- Dana DeLouis Win XP & Office 2003 "Paul Black" wrote in message ... 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 "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Paul Black" wrote in message ... 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-O140,COMBIN(49-O14,6),0)-IF(45-P140,COMBIN(49-P14,5),0 )-IF(46-Q140,COMBIN(49-Q14,4),0)-IF(47-R140,COMBIN(49-R14,3),0)-IF(48-S14 0,COMBIN(49-S14,2),0)-IF(49-T140,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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with a Formula Within a Function Please
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. -- Dana DeLouis Win XP & Office 2003 "Paul Black" wrote in message ... 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" wrote in message ... 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 -- Dana DeLouis Win XP & Office 2003 "Paul Black" wrote in message ... 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 "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Paul Black" wrote in message ... 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-O140,COMBIN(49-O14,6),0)-IF(45-P140,COMBIN(49-P14,5),0 )-IF(46-Q140,COMBIN(49-Q14,4),0)-IF(47-R140,COMBIN(49-R14,3),0)-IF(48-S14 0,COMBIN(49-S14,2),0)-IF(49-T140,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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Function formula (I think?) | Excel Worksheet Functions | |||
IF Function formula | Excel Worksheet Functions | |||
Help with OR function in IF function formula | Excel Worksheet Functions | |||
IF Function and a formula | Excel Worksheet Functions | |||
A formula/function to return a formula/function | Excel Worksheet Functions |