Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function for combinations
Suppose I have all possible combinations of a set of 7
numbers chosen 5 at a time. (i.e 7-choose-5). There would be a set of 21 total combos, which are listed below: Combo #1) 1-2-3-4-5 Combo #2) 1-2-3-4-6 Combo #3) 1-2-3-4-7 Combo #4) 1-2-3-5-6 Combo #5) 1-2-3-5-7 Combo #6) 1-2-3-6-7 Combo #7) 1-2-4-5-6 Combo #8) 1-2-4-5-7 Combo #9) 1-2-4-6-7 Combo #10 1-2-5-6-7 Combo #11) 1-3-4-5-6 Combo #12) 1-3-4-5-7 Combo #13) 1-3-4-6-7 Combo #14) 1-3-5-6-7 Combo #15) 1-4-5-6-7 Combo #16) 2-3-4-5-6 Combo #17) 2-3-4-5-7 Combo #18) 2-3-4-6-7 Combo #19) 2-3-5-6-7 Combo #20) 2-4-5-6-7 Combo #21) 3-4-5-6-7 I'm trying to devise some sort of function that will associate a unique identifier for each combination. So, if the combination of "1 2 3 4 5" is given as input, then my function should return a "1" (since this is the 1st combo in the list). If "1 3 4 5 7" is given as input, then the number 12 should be returned, since that is the 12th combo above. Does that make sense? I'm basically trying to find a way to generate a unique identifier for any 5-combination that is given as input. It would be nice if the solution could be applied to a general set of combos, such as N-choose-5. Does anyone have any ideas? I know this problem isn't really related to VBA, but I trust the wisdom of everybody here. Thanks everyone. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function for combinations
Hi Rob
I'm not that flash with Excel Combo boxes ( Access, not a problem ), that said! If you know how to tie the combobox to this then it maybe a winner for you. Sub myCombo() Dim ComboSelect As Range Dim ComboTag As Range Set ComboSelect = Range("A2:A23") 'Combobox range Set ComboTag = [B2] 'Corresponding Combo # output cell Select Case ComboSelect Case Is = "1-2-3-4-5" ComboTag.Value = 1 Case Is = "1-2-3-4-6" ComboTag.Value = 2 Case Is = "1-2-3-4-7" ComboTag.Value = 3 Case Is = "1-2-3-5-6" ComboTag.Value = 4 Case Is = "1-2-3-5-7" ComboTag.Value = 5 Case Is = "1-2-3-6-7" ComboTag.Value = 6 Case Is = "1-2-4-5-6" ComboTag.Value = 7 Case Is = "1-2-4-5-7" ComboTag.Value = 8 Case Is = "1-2-4-6-7" ComboTag.Value = 9 Case Is = "1-2-5-6-7" ComboTag.Value = 10 Case Is = "1-3-4-5-6" ComboTag.Value = 11 Case Is = "1-3-4-5-7" ComboTag.Value = 12 Case Is = "1-3-4-6-7" ComboTag.Value = 13 Case Is = "1-2-5-6-7" ComboTag.Value = 14 Case Is = "1-4-5-6-7" ComboTag.Value = 15 Case Is = "2-3-4-5-6" ComboTag.Value = 16 Case Is = "2-3-4-5-7" ComboTag.Value = 17 Case Is = "2-3-4-6-7" ComboTag.Value = 18 Case Is = "2-3-5-6-7" ComboTag.Value = 19 Case Is = "2-4-5-6-7" ComboTag.Value = 20 Case Is = "3-4-5-6-7" ComboTag.Value = 21 End Select End Sub Of course If I have it wrong, one of the many talented Guru's will correct it. HTH Mick. On 19/01/2012 8:37 PM, Robert Crandal wrote: Combo #1) 1-2-3-4-5 Combo #2) 1-2-3-4-6 Combo #3) 1-2-3-4-7 Combo #4) 1-2-3-5-6 Combo #5) 1-2-3-5-7 Combo #6) 1-2-3-6-7 Combo #7) 1-2-4-5-6 Combo #8) 1-2-4-5-7 Combo #9) 1-2-4-6-7 Combo #10 1-2-5-6-7 Combo #11) 1-3-4-5-6 Combo #12) 1-3-4-5-7 Combo #13) 1-3-4-6-7 Combo #14) 1-3-5-6-7 Combo #15) 1-4-5-6-7 Combo #16) 2-3-4-5-6 Combo #17) 2-3-4-5-7 Combo #18) 2-3-4-6-7 Combo #19) 2-3-5-6-7 Combo #20) 2-4-5-6-7 Combo #21) 3-4-5-6-7 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function for combinations
Robert Crandal brought next idea :
Suppose I have all possible combinations of a set of 7 numbers chosen 5 at a time. (i.e 7-choose-5). There would be a set of 21 total combos, which are listed below: Combo #1) 1-2-3-4-5 Combo #2) 1-2-3-4-6 Combo #3) 1-2-3-4-7 Combo #4) 1-2-3-5-6 Combo #5) 1-2-3-5-7 Combo #6) 1-2-3-6-7 Combo #7) 1-2-4-5-6 Combo #8) 1-2-4-5-7 Combo #9) 1-2-4-6-7 Combo #10 1-2-5-6-7 Combo #11) 1-3-4-5-6 Combo #12) 1-3-4-5-7 Combo #13) 1-3-4-6-7 Combo #14) 1-3-5-6-7 Combo #15) 1-4-5-6-7 Combo #16) 2-3-4-5-6 Combo #17) 2-3-4-5-7 Combo #18) 2-3-4-6-7 Combo #19) 2-3-5-6-7 Combo #20) 2-4-5-6-7 Combo #21) 3-4-5-6-7 I'm trying to devise some sort of function that will associate a unique identifier for each combination. So, if the combination of "1 2 3 4 5" is given as input, then my function should return a "1" (since this is the 1st combo in the list). If "1 3 4 5 7" is given as input, then the number 12 should be returned, since that is the 12th combo above. Does that make sense? I'm basically trying to find a way to generate a unique identifier for any 5-combination that is given as input. It would be nice if the solution could be applied to a general set of combos, such as N-choose-5. Does anyone have any ideas? I know this problem isn't really related to VBA, but I trust the wisdom of everybody here. Thanks everyone. Hi Robert, This smacks of Lottery Wheeling. My advice is to use an array for the combinations and use its index for the identifier. I have a VBA lottery wheel generator that assigns 'ticket#' as an identifier based on array index of the wheel combination, as follows... vTicketID(i, 0) = "Ticket" & CStr(i + 1) ...being that the array base is zero. This is the first element (vTicketID(0, 0), for example, of an abreviated wheel that generates 42 (5, 6 or 7 num) combinations with 5/6 odds. In a 7 num draw the UBound of the 2nd dim of the array is 7, giving an eight element array for the 1st ticket. UBound(vTicketID) is 41. So.., in your scenario you'd use a 6 element array for each combination as follows... For i = LBound(vCombos) To NumTicketsWheeled - 1 vCombos(i, 0) = "Combo #) & CStr(i + 1) vCombos(i, 1) = vWheel(0) vCombos(i, 2) = vWheel(1) vCombos(i, 3) = vWheel(2) vCombos(i, 4) = vWheel(3) vCombos(i, 5) = vWheel(4) Next 'i Alternatively, you could process each combination separately and write directly to the worksheet... Cells(i + 1, 1) = "Combo #" & CStr(i + 1) Cells(i + 1, 2).Resize(1, 5) = vWheel HTH -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function for combinations
"GS" wrote in message ...
This smacks of Lottery Wheeling. My advice is to use an array for the combinations and use its index for the identifier. For now, suppose that you do NOT have the full list of combos stored in an array. Suppose I gave you the following 5-combination as input: "15 23 26 29 40" Can you think of a good algorithm or scheme for computing the index number of this combination??? I'm looking for ways to assign unique identifiers for ANY general combination that might involve N numbers chosen K at a time. Does that make sense?? Thanks for your help again everyone. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function for combinations
hi Robert,
this is an example to get started Sub combin() Dim nbchiff As Integer, nb As Integer, plage As Range, cel As Range, list list = Array(15, 23, 26, 29, 40) nbchiff = InputBox("nombre de chiffre ?") For nb = 1 To nbchiff Cells(nb, 1) = (0 + list(nb - 1)) Next Cells(nbchiff, 1).Select bi: Set plage = Range(Cells(1, ActiveCell.Column), ActiveCell) plage.Cells(1).Offset(0, 1).Select For Each cel In plage For nb = 1 To nbchiff ActiveCell = cel & (",") & (1 + list(nb - 1)) ActiveCell.Offset(1, 0).Select Next nb Next cel ActiveCell.Offset(-1, 0).Select If ActiveCell.Column < nbchiff Then GoTo bi End Sub -- isabelle Le 2012-01-19 22:11, Robert Crandal a écrit : "GS" wrote in message ... This smacks of Lottery Wheeling. My advice is to use an array for the combinations and use its index for the identifier. For now, suppose that you do NOT have the full list of combos stored in an array. Suppose I gave you the following 5-combination as input: "15 23 26 29 40" Can you think of a good algorithm or scheme for computing the index number of this combination??? I'm looking for ways to assign unique identifiers for ANY general combination that might involve N numbers chosen K at a time. Does that make sense?? Thanks for your help again everyone. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function for combinations
you can increase performance by removing ".Select"
it is a macro "Excel4" that i found in my archives and i translated it as such in vba -- isabelle Le 2012-01-19 23:54, isabelle a écrit : hi Robert, this is an example to get started Sub combin() Dim nbchiff As Integer, nb As Integer, plage As Range, cel As Range, list list = Array(15, 23, 26, 29, 40) nbchiff = InputBox("nombre de chiffre ?") For nb = 1 To nbchiff Cells(nb, 1) = (0 + list(nb - 1)) Next Cells(nbchiff, 1).Select bi: Set plage = Range(Cells(1, ActiveCell.Column), ActiveCell) plage.Cells(1).Offset(0, 1).Select For Each cel In plage For nb = 1 To nbchiff ActiveCell = cel & (",") & (1 + list(nb - 1)) ActiveCell.Offset(1, 0).Select Next nb Next cel ActiveCell.Offset(-1, 0).Select If ActiveCell.Column < nbchiff Then GoTo bi End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function for combinations
on 1/19/2012, Robert Crandal supposed :
"GS" wrote in message ... This smacks of Lottery Wheeling. My advice is to use an array for the combinations and use its index for the identifier. For now, suppose that you do NOT have the full list of combos stored in an array. Suppose I gave you the following 5-combination as input: "15 23 26 29 40" Can you think of a good algorithm or scheme for computing the index number of this combination??? I'm looking for ways to assign unique identifiers for ANY general combination that might involve N numbers chosen K at a time. Does that make sense?? Thanks for your help again everyone. Using an array index will always give you a unique ID. You can also use a Collection object's 'Key', or the Scripting.Dictionary's 'Key' since both of those will always be unique because they will raises an error if you try to add dupes. (**Note** that VB's/VBA's Collection will process considerably faster than using Scripting's Dictionary) In any case, you need to increment a counter to create unique keys and so this brings us back to using an array. Possibly, you'll want to use a 1 based array rather than zero based. I use zero based because my wheeling combos are stored in ranges where the first cell is the wheel config, which always occupies array(0) so ticket numbering starts at array(1) on up to the UBound. So an array with UBound of 42 contains 43 elements; 0 + 42 tickets. The wheeling algorithms I use are the most popular used for 5+ odds, and are publicly available online. Just google "lotto wheels". There are many other configurations for various odds, though. How they work is for a given set of numbers (say 12, for example) how many 5, 6, or 7 number combinations can be created to return the odds using only the 12 numbers in the set. Sets to be wheeled can contain any number of numbers (I only use 16 max), and resulting combinations can contain any number of the numbers in the set. For example, you can generate 5 number combos using 20 number sets. The more numbers in a set the more combos can be generated. Depending on this criteria, the number of combos could be in the thousands if you want every possible combination. To get an idea of the magnitude, check out the odds of a 49 number lottery that draws 6 or even 7 numbers per winning ticket. I don't actually play the lottery, it's just fun to run wheels and check them against draw histories. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function for combinations
Excuse my ignorance, but what is that "nombre de chiffre" in the InputBox below?? "isabelle" wrote in message ... hi Robert, this is an example to get started Sub combin() Dim nbchiff As Integer, nb As Integer, plage As Range, cel As Range, list list = Array(15, 23, 26, 29, 40) nbchiff = InputBox("nombre de chiffre ?") For nb = 1 To nbchiff Cells(nb, 1) = (0 + list(nb - 1)) Next Cells(nbchiff, 1).Select bi: Set plage = Range(Cells(1, ActiveCell.Column), ActiveCell) plage.Cells(1).Offset(0, 1).Select For Each cel In plage For nb = 1 To nbchiff ActiveCell = cel & (",") & (1 + list(nb - 1)) ActiveCell.Offset(1, 0).Select Next nb Next cel ActiveCell.Offset(-1, 0).Select If ActiveCell.Column < nbchiff Then GoTo bi End Sub -- isabelle Le 2012-01-19 22:11, Robert Crandal a écrit : "GS" wrote in message ... This smacks of Lottery Wheeling. My advice is to use an array for the combinations and use its index for the identifier. For now, suppose that you do NOT have the full list of combos stored in an array. Suppose I gave you the following 5-combination as input: "15 23 26 29 40" Can you think of a good algorithm or scheme for computing the index number of this combination??? I'm looking for ways to assign unique identifiers for ANY general combination that might involve N numbers chosen K at a time. Does that make sense?? Thanks for your help again everyone. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function for combinations
"GS" wrote in message ... Using an array index will always give you a unique ID. You can also use a Collection object's 'Key', or the Scripting.Dictionary's 'Key' since both of those will always be unique because they will raises an error if you try to add dupes. (**Note** that VB's/VBA's Collection will process considerably faster than using Scripting's Dictionary) In any case, you need to increment a counter to create unique keys and so this brings us back to using an array. Possibly, you'll want to use a 1 based array rather than zero based. I use zero based because my wheeling combos are stored in ranges where the first cell is the wheel config, which always occupies array(0) so ticket numbering starts at array(1) on up to the UBound. So an array with UBound of 42 contains 43 elements; 0 + 42 tickets. Hmmmm, I'm looking for a solution that still doesn't involve the use of array indexes.... I'll try to rephrase my question again, but with a different scenario. (My bad for not being clear in the first place) Suppose that my lottery contains the numbers 1 through 39, and only 5 numbers are drawn, which means that this lottery contains 575,757 unique combinations. My program will NEVER attempt to generate all possible 575,757 combos, therefore, using an array index is not an option All my program does at the moment is ask the user to input any 5-combo they choose, in the form of a string or whatever. If the user enters a string of "1 2 3 5 10", how can you calculate that this combo might occur at position #24 in the combo list (without using an array)? How might you be able to calculate that combo "1 2 3 4 5" occurs at position #1?? It would be ideal if I could calculate either the "postion number" of any combo, or simply calculate any type of unique identifier for each combo. Does that make more sense? Somebody pointed me in the direction of the following website for a solution: http://en.wikipedia.org/wiki/Combina..._number_system The problem is, I don't really understand much of the jargon or notations on that webpage, but it seems to be related to the problem I have. Sorry if I confused u again, Mr. GS. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function for combinations
oops sorry,
replace nbchiff = InputBox("nombre de chiffre ?") by nbchiff = 5 -- isabelle Le 2012-01-20 05:11, Robert Crandal a écrit : Excuse my ignorance, but what is that "nombre de chiffre" in the InputBox below?? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function for combinations
Robert,
I just took a look at the code behind my "AddTicket" procedure to see exactly how I handled ticket numbering. Turns out my code inserts a formula as follows... =TicketNumber ...which took me to the Define Name dialog to see how this was constructed. Turns out this uses row position relative to the header row above where the first draw record starts. Draw win nums are listed on the left side of the wks. Ticket nums/combos are listed to the right of the draw history cols. Analysis/Wheeling section is to the right of tickets list. The RefersTo for the defined name formula "TicketNumber" is... ="Ticket #"&(ROW()-ROW('649'!TicketsToCheck_Hdr)) ...where "TicketsToCheck_Hdr is located in Rows(6), and so tickets start listing in Rows(7). So the above formula simply subtracts 6 from whatever row the formula is in and concatenates the result to the text shown. I'm sure I did this so I could re-order ticket combos if generating 'Quick Picks' instead (or along with) wheeled combos using a preferred list of nums akin to 'hot' or 'fav#' as determined by criteria in the analysis area. This RefersTo refs the locally scoped defined name range on Sheets("649"). Sheets("749") also contains a range with the same locally scoped defined name, as would any other sheet for any other draw. Since our national scope draws are only 2 in count (649 <6/49 & LottoMax <7/49), these are the only lottery games I'm tracking. There's no reason, though, why a Sheets("539") can't be added to the wkb. I haven't included any 5/39 or 6/39 wheels but I know tonnes of them exist out there in cyberspace. Each sheet contains a DV dropdown in the Wheeling section so I can choose which wheel config to use for generating ticket combos. The list only contains those wheel configs that apply to the lottery game for that sheet. The wheel algorithms are stored on a hidden sheet in horizontal ranges, and are grouped according to the game they apply to (ie: 649, 749). For example, wheel config '12Numbers 42Tickets 5/6Odds' will be used to generate 42 tickets using 12 specified numers that could yield 5/6 odds if all nums drawn are in the 12 nums specified. The specified list can be prioritized for concentration purposes since these wheel algorithms are 'abbreviated', meaning they don't generate every possible num combo. -- I'm not sure I follow when you state... "If the user enters a string of "1 2 3 5 10", how can you calculate that this combo might occur at position #24 in the combo list (without using an array)? How might you be able to calculate that combo "1 2 3 4 5" occurs at position #1??" ...which suggests that each combo may have some criteria that determines where it 'belongs' in the list. 'Next ticket#' is my approach. Does this help? -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function for combinations
"GS" wrote in message ... I don't actually play the lottery, it's just fun to run wheels and check them against draw histories. It's okay to play the lottery. What if your wheel hits, but you never bet any money on it?? You're gonna feel bad forever. Which games do you usually wheel?? 6 number games with 42 numbers??? just curious |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function for combinations
On Jan 20, 6:59*am, "Robert Crandal" wrote:
"GS" wrote in .... Using an array index will always give you a unique ID. You can also use a Collection object's 'Key', or the Scripting.Dictionary's 'Key' since both of those will always be unique because they will raises an error if you try to add dupes. (**Note** that VB's/VBA's Collection will process considerably faster than using Scripting's Dictionary) In any case, you need to increment a counter to create unique keys and so this brings us back to using an array. Possibly, you'll want to use a 1 based array rather than zero based. I use zero based because my wheeling combos are stored in ranges where the first cell is the wheel config, which always occupies array(0) so ticket numbering starts at array(1) on up to the UBound. So an array with UBound of 42 contains 43 elements; 0 + 42 tickets. Hmmmm, I'm looking for a solution that still doesn't involve the use of array indexes.... *I'll try to rephrase my question again, but with a different scenario. * (My bad for not being clear in the first place) Suppose that my lottery contains the numbers 1 through 39, and only 5 numbers are drawn, which means that this lottery contains 575,757 unique combinations. * My program will NEVER attempt to generate all possible 575,757 combos, therefore, using an array index is not an option All my program does at the moment is ask the user to input any 5-combo they choose, in the form of a string or whatever. * If the user enters a string of "1 2 3 5 10", how can you calculate that this combo might occur at position #24 in the combo list (without using an array)? * How might you be able to calculate that combo "1 2 3 4 5" occurs at position #1?? *It would be ideal if I could calculate either the "postion number" of any combo, or simply calculate any type of unique identifier for each combo. Does that make more sense? Somebody pointed me in the direction of the following website for a solution: *http://en.wikipedia.org/wiki/Combina..._number_system The problem is, I don't really understand much of the jargon or notations on that webpage, but it seems to be related to the problem I have. Sorry if I confused u again, Mr. GS. Greetings: I wrote the following function from that Wikipedia page. I tested it on all 10 combinations of 3 numbers drawn from {1,2,3,4,5} and it seems to work. The order it returns is not the same order that you asked for in your original post but it succeeds in returning a unique number in the range 1 to (n choose k) for all combinations of k elements given as space-delimited strings where the numbers are listed in increasing order: Function CRank(combo As String) As Long Dim r As Long, i As Long, j As Long, k As Long Dim nums As Variant nums = Split(combo) k = UBound(nums) r = 1 For i = 1 To k + 1 j = Int(nums(i - 1)) If j i Then r = r + Application.WorksheetFunction.Combin(j - 1, i) End If Next i CRank = r End Function Hope that helps |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function for combinations
On Jan 21, 9:27*am, John Coleman wrote:
On Jan 20, 6:59*am, "Robert Crandal" wrote: "GS" wrote in ... Using an array index will always give you a unique ID. You can also use a Collection object's 'Key', or the Scripting.Dictionary's 'Key' since both of those will always be unique because they will raises an error if you try to add dupes. (**Note** that VB's/VBA's Collection will process considerably faster than using Scripting's Dictionary) In any case, you need to increment a counter to create unique keys and so this brings us back to using an array. Possibly, you'll want to use a 1 based array rather than zero based. I use zero based because my wheeling combos are stored in ranges where the first cell is the wheel config, which always occupies array(0) so ticket numbering starts at array(1) on up to the UBound. So an array with UBound of 42 contains 43 elements; 0 + 42 tickets. Hmmmm, I'm looking for a solution that still doesn't involve the use of array indexes.... *I'll try to rephrase my question again, but with a different scenario. * (My bad for not being clear in the first place) Suppose that my lottery contains the numbers 1 through 39, and only 5 numbers are drawn, which means that this lottery contains 575,757 unique combinations. * My program will NEVER attempt to generate all possible 575,757 combos, therefore, using an array index is not an option All my program does at the moment is ask the user to input any 5-combo they choose, in the form of a string or whatever. * If the user enters a string of "1 2 3 5 10", how can you calculate that this combo might occur at position #24 in the combo list (without using an array)? * How might you be able to calculate that combo "1 2 3 4 5" occurs at position #1?? *It would be ideal if I could calculate either the "postion number" of any combo, or simply calculate any type of unique identifier for each combo. Does that make more sense? Somebody pointed me in the direction of the following website for a solution: *http://en.wikipedia.org/wiki/Combina..._number_system The problem is, I don't really understand much of the jargon or notations on that webpage, but it seems to be related to the problem I have. Sorry if I confused u again, Mr. GS. Greetings: I wrote the following function from that Wikipedia page. I tested it on all 10 combinations of 3 numbers drawn from {1,2,3,4,5} and it seems to work. The order it returns is not the same order that you asked for in your original post but it succeeds in returning a unique number in the range 1 to (n choose k) for all combinations of k elements given as space-delimited strings where the numbers are listed in increasing order: Function CRank(combo As String) As Long * * Dim r As Long, i As Long, j As Long, k As Long * * Dim nums As Variant * * nums = Split(combo) * * k = UBound(nums) * * r = 1 * * For i = 1 To k + 1 * * * * j = Int(nums(i - 1)) * * * * If j i Then * * * * * * r = r + Application.WorksheetFunction.Combin(j - 1, i) * * * * End If * * Next i * * CRank = r End Function Hope that helps- Hide quoted text - - Show quoted text - Here is another approach that returns the ordering from your original post: Function Choose(n As Long, k As Long) As Long If n < k Then Choose = 0 Else Choose = Application.WorksheetFunction.Combin(n, k) End If End Function Function ComboRank(combo As String, n As Long) As Long Dim i As Long, k As Long Dim nums As Variant Dim sum As Long nums = Split(combo) k = UBound(nums) For i = 0 To k sum = sum + Choose(n - Int(nums(i)), k + 1 - i) Next i ComboRank = Choose(n, k + 1) - sum End Function With this approach you need to specify the number of elements in the set being chosen from (7 in your original example) For example, ComboRank("1 3 4 5 6",7) returns 11, which corresponds to 1-3-4-5-6 being the 11th entry in your original list |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function for combinations
Robert Crandal submitted this idea :
"GS" wrote in message ... I don't actually play the lottery, it's just fun to run wheels and check them against draw histories. It's okay to play the lottery. What if your wheel hits, but you never bet any money on it?? You're gonna feel bad forever. Which games do you usually wheel?? 6 number games with 42 numbers??? just curious Well, I'm living on a pension and so I have to budget my spending. Adding to the $$ burden is the reality of living with ALS (Lou Gehrig's), which has unbelievable financial burdens attached. I just monitor/track our (Canada's) 2 national lotteries to see how the wheeling algorithms fair after each draw. The '6/49' (Lotto649) happens Wed/Sat and the 7/49 (LottoMax) happens on Fri. Did you check John Coleman's offering for generating numbers from a chosen list? Might be good way to do 'selective' Quick Picks as an alternative to wheeling. My app's Quick Picks select from all 49 nums same as the lotto terminals do. John's function might be worth adding (with some modification) to my app as an 'extra' feature in the wheeling section. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function for combinations
GS was thinking very hard :
Robert Crandal submitted this idea : "GS" wrote in message ... I don't actually play the lottery, it's just fun to run wheels and check them against draw histories. It's okay to play the lottery. What if your wheel hits, but you never bet any money on it?? You're gonna feel bad forever. Which games do you usually wheel?? 6 number games with 42 numbers??? just curious Well, I'm living on a pension and so I have to budget my spending. Adding to the $$ burden is the reality of living with ALS (Lou Gehrig's), which has unbelievable financial burdens attached. I just monitor/track our (Canada's) 2 national lotteries to see how the wheeling algorithms fair after each draw. The '6/49' (Lotto649) happens Wed/Sat and the 7/49 (LottoMax) happens on Fri. Did you check John Coleman's offering for generating numbers from a chosen list? Might be good way to do 'selective' Quick Picks as an alternative to wheeling. My app's Quick Picks select from all 49 nums same as the lotto terminals do. John's function might be worth adding (with some modification) to my app as an 'extra' feature in the wheeling section. Well.., I see that this nor John's 2nd offering addresses the 'selective' Quick Pick issue. Not sure, though, if it's how you want to index your combos... -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup plus other function/s combinations required? | Excel Worksheet Functions | |||
Permutations or Combinations or some other function?? | Excel Discussion (Misc queries) | |||
Function generating all possible combinations of set of numbers | Excel Worksheet Functions | |||
Keyboard Shortcuts combinations of function keys, c... | Excel Worksheet Functions | |||
UDF's and Worksheet Function VBA Combinations | Excel Programming |