 Calculate Sets and Subsets Index ‹ Excel ‹ Excel Programming
Author Message
Vidal   Posted: Mon Jul 16 13:24:02 CDT 2007 Top
 Excel Programming >> Calculate Sets and Subsets Hi everyone, I have tried to accomplish the following for about two years now but without any success. This is for a 6 numbers drawn from 49 Lotto without replacement. I have a list of 6 number combinations in an Excel sheet named "Data" in Cells "B3:G50" ( the combinations will always start in Cell "B3" BUT the Cell "G50" will change depending on the number of combinations to evaluate ). I have a sheet named "Statistics" where the criteria to use is stored. The criteria to use is as follows :- Cell "E3" = Total Numbers Drawn ( 6 for example, this could be less or more ). Cell "E4" = Total Numbers Selected ( 9 for example, this could be less or more ) Lets assume that the first 3 combinations are as follows :- 01 02 03 04 05 06 01 02 03 07 08 09 03 05 06 07 08 09 The maximum number used on this occasion is 9. What i would like the program to do is calculate the unique combinations of 6 numbers from 9 which will be used for the basis of the program. There are 14 categories of Sets and Subsets for each 6 number combination. I would like to get a grand total of the combinations covered for each of the categories below. The grand totals for each category will go in the sheet named "Statistics" in Cells :- Cell "D09" = 2 if 2 Cell "D10" = 2 if 3 Cell "D11" = 2 if 4 Cell "D12" = 2 if 5 Cell "D13" = 2 if 6 Cell "D14" = 3 if 3 Cell "D15" = 3 if 4 Cell "D16" = 3 if 5 Cell "D17" = 3 if 6 Cell "D18" = 4 if 4 Cell "D19" = 4 if 5 Cell "D20" = 4 if 6 Cell "D21" = 5 if 5 Cell "D22" = 5 if 6 To achieve this, EACH Set and Subset needs to be run against EACH combination in turn, starting from the first one in Cells "B3:G3" in the sheet named "Data" and continuing down. The 3 if 5 category for example, involves cycling through ALL the 5 number combinations that can be produced from the 9 numbers and comparing EACH of them with EACH of the combinations in the above list in turn to see if that particular 5 number combination matches the 5 number combination with *EXACTLY* 3 numbers. If it does, then that Combination of 3 if 5 is covered and 1 ( One ) is added to that categories grand total and there is NO need to continue to check for that particular combinations 3 if 5 cover any further so go onto the next 3 if 5 combination to check. ******************************************************************************** This is what I found somewhere that might shed some light on what I am trying to achieve :- We have a list of combinations C(n,k,t,m)=b where :- n = the maximum ball number in our list ( e.g. 9 ). k = the number of balls drawn ( e.g. a 6 ball game has k=6 ). t = the minimum number we want to guarantee a win ( e.g. 3 ). m = the condition that has to be met in order to guarantee the t prize division win, m defines the least number of balls from our n set that must be correct ( e.g. 5 ). b = the total tickets required to play. Now, if you are interested to find the total coverage achieved in a certain category e.g. "x" if "y", then the total combinations that need to be covered are nCk(n,y)=A. Thus, you have to test "A" combinations, each one containing "y" numbers against the combinations in the list ( each combination contains k numbers ). A combination of those "A" is covered if there is at least one combination in your list, that contains at least "x" numbers in common. All you have to do is to go through all "A" combinations and test each of them to see if it contains at least "x" numbers in common with at least one combination in your list of combinations. If it does, then it is covered. ******************************************************************************** I have made a start on the programming ( probably not the best way to write this ) ... Option Explict Option Base 1 Sub Produce_Statistics() Dim A as Integer Dim B as Integer Dim C as Integer Dim D as Integer Dim E as Integer Dim F as Integer Dim MinVal As Integer Dim MaxVal As Integer Application.ScreenUpdating = False MinVal = 1 MaxVal = WorkSheets.("Statistics").Range("E4").Value For A = 1 to MaxVal - 5 For B = A + 1 to MaxVal - 4 For C = B + 1 to MaxVal - 3 For D = C + 1 to MaxVal - 2 For E = D + 1 to MaxVal - 1 For F = E + 1 to MaxVal *** Code goes here maybe *** Next F Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub I am new to VBA so have no idea how to accomplish this. Thanks in Advance. All the Best. Paul Excel9 MikeH   Posted: Mon Jul 16 13:24:02 CDT 2007 Top
 Excel Programming >> Calculate Sets and Subsets Paul, I did this years ago in quick basic when the UK lottery started and here's the start of the conversion to Excel. This gives every possible combination of 6 from x numbers. (x is the variable fullset). At the moment it uses the numbers 1 - x and perms any 6 from those but it should be fairly straightforward to make it use any 10 numbers a user enters but i've run out of time right now. Hope this gets you started. Sub permit() Dim fullset As Integer Dim maxpossible As Long Dim found() As String Dim n As Long, n1 As Long, n2 As Long, n3 As Double Dim n4 As Long, n5 As Long, n6 As Double fullset = 12 maxpossible = 65535 ReDim found(maxpossible) n = 1 For n1 = 1 To fullset For n2 = 2 To fullset If n2 > n1 Then For n3 = 3 To fullset If n3 > n2 Then For n4 = 4 To fullset If n4 > n3 Then For n5 = 5 To fullset If n5 > n4 Then For n6 = 6 To fullset If n6 > n5 Then If (n6 <> n5) And (n5 <> n4) And (n4 <> n3) And (n3 <> n2) And (n2 <> n1) Then found(n) = CStr(n1) & "," & CStr(n2) & "," & CStr(n3) & "," & CStr(n4) & "," & CStr(n5) & "," & CStr(n6) n = n + 1 End If End If Next n6 End If Next n5 End If Next n4 End If Next n3 End If Next n2 Next n1 For n = 1 To maxpossible Cells(n, 1).Value = found(n) Next n End Sub Mike > Hi everyone, > > I have tried to accomplish the following for about two years now but > without any success. > This is for a 6 numbers drawn from 49 Lotto without replacement. > I have a list of 6 number combinations in an Excel sheet named "Data" > in Cells "B3:G50" ( the combinations will always start in Cell "B3" > BUT the Cell "G50" will change depending on the number of combinations > to evaluate ). > I have a sheet named "Statistics" where the criteria to use is stored. > The criteria to use is as follows :- > > Cell "E3" = Total Numbers Drawn ( 6 for example, this could be less or > more ). > Cell "E4" = Total Numbers Selected ( 9 for example, this could be less > or more ) > > Lets assume that the first 3 combinations are as follows :- > > 01 02 03 04 05 06 > 01 02 03 07 08 09 > 03 05 06 07 08 09 > > The maximum number used on this occasion is 9. What i would like the > program to do is calculate the unique combinations of 6 numbers from 9 > which will be used for the basis of the program. > There are 14 categories of Sets and Subsets for each 6 number > combination. I would like to get a grand total of the combinations > covered for each of the categories below. The grand totals for each > category will go in the sheet named "Statistics" in Cells :- > > Cell "D09" = 2 if 2 > Cell "D10" = 2 if 3 > Cell "D11" = 2 if 4 > Cell "D12" = 2 if 5 > Cell "D13" = 2 if 6 > Cell "D14" = 3 if 3 > Cell "D15" = 3 if 4 > Cell "D16" = 3 if 5 > Cell "D17" = 3 if 6 > Cell "D18" = 4 if 4 > Cell "D19" = 4 if 5 > Cell "D20" = 4 if 6 > Cell "D21" = 5 if 5 > Cell "D22" = 5 if 6 > > To achieve this, EACH Set and Subset needs to be run against EACH > combination in turn, starting from the first one in Cells "B3:G3" in > the sheet named "Data" and continuing down. > The 3 if 5 category for example, involves cycling through ALL the 5 > number combinations that can be produced from the 9 numbers and > comparing EACH of them with EACH of the combinations in the above list > in turn to see if that particular 5 number combination matches the 5 > number combination with *EXACTLY* 3 numbers. If it does, then that > Combination of 3 if 5 is covered and 1 ( One ) is added to that > categories grand total and there is NO need to continue to check for > that particular combinations 3 if 5 cover any further so go onto the > next 3 if 5 combination to check. > > ******************************************************************************** > > This is what I found somewhere that might shed some light on what I am > trying to achieve :- > > We have a list of combinations C(n,k,t,m)=b where :- > > n = the maximum ball number in our list ( e.g. 9 ). > k = the number of balls drawn ( e.g. a 6 ball game has k=6 ). > t = the minimum number we want to guarantee a win ( e.g. 3 ). > m = the condition that has to be met in order to guarantee the t prize > division win, m defines the least number of balls from our n set that > must be correct ( e.g. 5 ). > b = the total tickets required to play. > > Now, if you are interested to find the total coverage achieved in a > certain category e.g. "x" if "y", then the total combinations that > need to be covered are nCk(n,y)=A. Thus, you have to test "A" > combinations, each one containing "y" numbers against the combinations > in the list ( each combination contains k numbers ). > A combination of those "A" is covered if there is at least one > combination in your list, that contains at least "x" numbers in > common. All you have to do is to go through all "A" combinations and > test each of them to see if it contains at least "x" numbers in common > with at least one combination in your list of combinations. If it > does, then it is covered. > > ******************************************************************************** > > I have made a start on the programming ( probably not the best way to > write this ) ... > > Option Explict > Option Base 1 > > Sub Produce_Statistics() > > Dim A as Integer > Dim B as Integer > Dim C as Integer > Dim D as Integer > Dim E as Integer > Dim F as Integer > Dim MinVal As Integer > Dim MaxVal As Integer > > Application.ScreenUpdating = False > > MinVal = 1 > MaxVal = WorkSheets.("Statistics").Range("E4").Value > > For A = 1 to MaxVal - 5 > For B = A + 1 to MaxVal - 4 > For C = B + 1 to MaxVal - 3 > For D = C + 1 to MaxVal - 2 > For E = D + 1 to MaxVal - 1 > For F = E + 1 to MaxVal > > *** Code goes here maybe *** > > Next F > Next E > Next D > Next C > Next B > Next A > > Application.ScreenUpdating = True > End Sub > > I am new to VBA so have no idea how to accomplish this. > > Thanks in Advance. > All the Best. > Paul > > Paul   Posted: Tue Jul 17 06:09:14 CDT 2007 Top Paul   Posted: Wed Jul 18 04:35:38 CDT 2007 Top Paul   Posted: Sat Jul 21 05:32:27 CDT 2007 Top  Index ‹ Excel ‹ Excel Programming