Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Subsets with Specific Criteria
Hi everyone,
I would like an algorithm or code to work out how many subset combinations with specific criteria are covered when compared with combinations in a wheel. A wheels criteria is as follows :- Description = C(n,k,t,m)=b where :- n=the number of balls drawn from (i.e. 49). k=the number of balls drawn (i.e. 6). t=the total balls to match to guarantee a win. m=the criteria that has to be met in order to guarantee t win, m defines the least number of balls from our n set that must be correct. b=the total combinations in the wheel. The formula to calculate the total combinations is :- .........................n! C(n, k) = ------------------- ....................k! (n - k) ! The formula to calculate the subsets (the coefficient) is ... .........................n! C(n, m) = ------------------- ....................m! (n - m) ! .... where n! is n factorial and is equal to n*(n-1)*(n-2)* ... *2*1. The above was taken from :- http://mathforum.org/library/drmath/view/60881.html I manually input combinations of 6 numbers into a spreadsheet named "Data" in cells "B3:G?". We will use the 2 if 5, 3 if 5, 4 if 5 and 5 if 5 categories to explain what I am trying to achieve. For the 2 if 5 category, this requires calculating ALL the 5 number combinations available from "n" which would be C(n, k) where "n" is 49 (or the maximum number used in the wheel) and "k" is 5. The category 2 if 5 means, that "IF" at "LEAST" 2 numbers in ANY of the 5 number combinations matches 2 numbers in ANY of the 6 number combinations in the wheel, then those 5 number combinations have satisfied the 2 if 5 scenario and are therefore covered. You then add 1 to the 2 if 5 category total for each of the 5 number combinations that has satisfied the 2 if 5 scenario. For the 2 if 5 scenario, there is NO need to list the C(n,5) combinations because I ONLY want the total combinations covered for the 2 if 5 category, not the actual combinations themselves. Anyway, the 5 number combinations ONLY need to be calculated for the highest "n" number in the wheel, this could be 12 [ C(12,5) ], 15 [ C15,5) ], 20 [ C(20,5) ], 30 [ (30,5) ] etc. Basically, the program needs to iterate through ALL the 5 number combinations from "n" and check each one to see if there is at "LEAST" 2 numbers in ANY of the 6 number combinations in the spreadsheet. If there is, 1 is added to the category covered total for each 5 number combination that satisfies the 2 if 5 scenario. The same principle and structure applies for the 3 if 5, 4 if 5 and 5 if 5 categories. If we use the 5 number combinations as "x" for example, and the 6 number combinations as "y" for example, this is roughly what should happen :- If x matches y in = 2 numbers Then Category 2 if 5 Total = + 1 If x matches y in = 3 numbers Then Category 3 if 5 Total = + 1 If x matches y in = 4 numbers Then Category 4 if 5 Total = + 1 If x matches y in EXACTLY 5 numbers Then Category 5 if 5 Total = + 1 End If End If End If End If Thanks in Advance. All the Best. Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Subsets with Specific Criteria
Hi,
Maybe the code to produce the 5 number combinations would look something like this :- Option Explicit Option Base 1 Sub Produce_5_Number_Combinations() Dim A as Integer Dim B as Integer Dim C as Integer Dim D as Integer Dim E as Integer Dim MinVal As Integer Dim MaxVal As Integer Application.ScreenUpdating = False MinVal = 1 MaxVal = Whatever the highest number is in the sheet named "Data" and in the Range "B3:G?". For A = 1 to MaxVal - 4 For B = A + 1 to MaxVal - 3 For C = B + 1 to MaxVal - 2 For D = C + 1 to MaxVal - 1 For E = D + 1 to MaxVal *** Code for the ? if 5 scenario goes here maybe *** Next E Next D Next C Next B Next A *** The 2 if 5 category total will go in the sheet named "Statistics" and in Cell "D12" *** Application.ScreenUpdating = True End Sub Thanks in Advance. All the Best. Paul On Jul 26, 3:50 pm, Paul Black wrote: Hi everyone, I would like an algorithm or code to work out how many subset combinations with specific criteria are covered when compared with combinations in a wheel. A wheels criteria is as follows :- Description = C(n,k,t,m)=b where :- n=the number of balls drawn from (i.e. 49). k=the number of balls drawn (i.e. 6). t=the total balls to match to guarantee a win. m=the criteria that has to be met in order to guarantee t win, m defines the least number of balls from our n set that must be correct. b=the total combinations in the wheel. The formula to calculate the total combinations is :- ........................n! C(n, k) = ------------------- ...................k! (n - k) ! The formula to calculate the subsets (the coefficient) is ... ........................n! C(n, m) = ------------------- ...................m! (n - m) ! ... where n! is n factorial and is equal to n*(n-1)*(n-2)* ... *2*1. The above was taken from :-http://mathforum.org/library/drmath/view/60881.html I manually input combinations of 6 numbers into a spreadsheet named "Data" in cells "B3:G?". We will use the 2 if 5, 3 if 5, 4 if 5 and 5 if 5 categories to explain what I am trying to achieve. For the 2 if 5 category, this requires calculating ALL the 5 number combinations available from "n" which would be C(n, k) where "n" is 49 (or the maximum number used in the wheel) and "k" is 5. The category 2 if 5 means, that "IF" at "LEAST" 2 numbers in ANY of the 5 number combinations matches 2 numbers in ANY of the 6 number combinations in the wheel, then those 5 number combinations have satisfied the 2 if 5 scenario and are therefore covered. You then add 1 to the 2 if 5 category total for each of the 5 number combinations that has satisfied the 2 if 5 scenario. For the 2 if 5 scenario, there is NO need to list the C(n,5) combinations because I ONLY want the total combinations covered for the 2 if 5 category, not the actual combinations themselves. Anyway, the 5 number combinations ONLY need to be calculated for the highest "n" number in the wheel, this could be 12 [ C(12,5) ], 15 [ C15,5) ], 20 [ C(20,5) ], 30 [ (30,5) ] etc. Basically, the program needs to iterate through ALL the 5 number combinations from "n" and check each one to see if there is at "LEAST" 2 numbers in ANY of the 6 number combinations in the spreadsheet. If there is, 1 is added to the category covered total for each 5 number combination that satisfies the 2 if 5 scenario. The same principle and structure applies for the 3 if 5, 4 if 5 and 5 if 5 categories. If we use the 5 number combinations as "x" for example, and the 6 number combinations as "y" for example, this is roughly what should happen :- If x matches y in = 2 numbers Then Category 2 if 5 Total = + 1 If x matches y in = 3 numbers Then Category 3 if 5 Total = + 1 If x matches y in = 4 numbers Then Category 4 if 5 Total = + 1 If x matches y in EXACTLY 5 numbers Then Category 5 if 5 Total = + 1 End If End If End If End If Thanks in Advance. All the Best. Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating subsets | Excel Discussion (Misc queries) | |||
Calculate the Number of Subsets | Excel Programming | |||
generate all subsets (2^n array) | Excel Programming | |||
Getting subsets of data | Excel Programming |