Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Macro
I would like to create a macro that gives the different results for the
following Question: I have 20 numbers (1-20) that are broken down into three groups. How can I create a macro that generates different sets of 3 groups of numbers, that when any 5 numbers are picked at random, at least four (80%) of the five numbers will be in a group? Each number is used once per set. The objective is to generate the least amount of sets to accomplish this goal. Partial Answer: Set 1 Group 1 = 1, 2, 3, 4, 5, 6, 7 Group 2 = 8, 9, 10, 11, 12, 13, 14 Group 3 = 15, 16, 17, 18, 19, 20 Set 2 Group 1 = 20, 14, 6, 18, 10, 12, 4 Group 2 = 16, 8, 2, 3, 7, 11, 15 Group 3 = 1, 5, 9, 11, 17, 19 Set 3 Group 1 = 3, 6, 9, 12, 15, 18 1 Group 2 = 4, 7, 10, 13, 16, 19, 5 Group 3 = 2, 8, 11, 14, 17, 20 And so on¦¦¦¦. In other words, Question: I have 20 employees that on average 5 of them will complain about different working conditions (or other problems) per month. Each employee will be listed/named 1-20 in excel. The 20 employees are broken down into 3 groups. How many different sets of 3 groups (numbered 1-20 where each number can only be used once per set) will I have to create to have at least 4 out of 5 random complaints end up in the same group? The objective is to accomplish this goal by using the least amount of sets. Can I create a macro for this? I'm using Microsoft Excel 2002. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Macro
I just wrote the code below to estimate the probability that 4 or 5 of 5
complaints come from the same group, assuming that that each person is equally likely to complain and that therefore the probability that a complaint comes from a given group is determined by its size. With 2 groups of size 7 and one of size 6, the result was approximately 13.9%. Am I correct that you are asking if there is a way to group the people such that EVERY MONTH, 80% or more of the complaints come from the same group? That would mean that an event that has only a ~14% chance of occurring happens every month. The chance of that happening for 3 months running is 0.14^3 = 0.002744, or about 3 in 1,000. IOW, your scenario is very unlikely UNLESS you have some real "complainers" among the 20 and you put them all into the same group (or the members of the group work in the same department and that department has real problems). i.e. the groups are NOT constructed randomly. You don't need a computer for that. Or am I missing the point entirely? ' Simulation of source of complaints ' There are 3 groups, of size 7, 7, and 6 ' There are 5 complaints per month, ' Calculate long-run probability that 4 or 5 of ' the 5 complaints all come from the same group Option Explicit Sub Complaints() Dim Four As Double Dim i As Long Dim j As Long Dim k As Long Dim NumTries As Long Dim S(1 To 3) As Long Dim T As Long Dim X As Double Randomize Timer NumTries = 1000000# For i = 1 To NumTries Erase S() For j = 1 To 5 '5 complaints per month X = Rnd() Select Case X 'determine group from which it came Case Is < 0.35: S(1) = S(1) + 1 'group 1 has 7/20 people = 35% Case Is < 0.70: S(2) = S(2) + 1 'group 2 has 7/20 people = 35% Case Else: S(3) = S(3) + 1 'the rest are in group 3 = 30% End Select Next j 'are there 4 or 5 in the same group? 'if so, count this as a "success" For j = 1 To 3 If S(j) = 4 Then Four = Four + 1 Exit For End If Next j Next i Debug.Print Format$(Four / NumTries, "0.00%") End Sub On Sat, 5 Mar 2005 00:53:02 -0800, "DeRizzio" wrote: I would like to create a macro that gives the different results for the following Question: I have 20 numbers (1-20) that are broken down into three groups. How can I create a macro that generates different sets of 3 groups of numbers, that when any 5 numbers are picked at random, at least four (80%) of the five numbers will be in a group? Each number is used once per set. The objective is to generate the least amount of sets to accomplish this goal. Partial Answer: Set 1 Group 1 = 1, 2, 3, 4, 5, 6, 7 Group 2 = 8, 9, 10, 11, 12, 13, 14 Group 3 = 15, 16, 17, 18, 19, 20 Set 2 Group 1 = 20, 14, 6, 18, 10, 12, 4 Group 2 = 16, 8, 2, 3, 7, 11, 15 Group 3 = 1, 5, 9, 11, 17, 19 Set 3 Group 1 = 3, 6, 9, 12, 15, 18 1 Group 2 = 4, 7, 10, 13, 16, 19, 5 Group 3 = 2, 8, 11, 14, 17, 20 And so on . In other words, Question: I have 20 employees that on average 5 of them will complain about different working conditions (or other problems) per month. Each employee will be listed/named 1-20 in excel. The 20 employees are broken down into 3 groups. How many different sets of 3 groups (numbered 1-20 where each number can only be used once per set) will I have to create to have at least 4 out of 5 random complaints end up in the same group? The objective is to accomplish this goal by using the least amount of sets. Can I create a macro for this? I'm using Microsoft Excel 2002. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Macro
Hello Myrna, Thank you for your assistance.
However, Im still not sure how to generate these results/sets. When it comes to using VBE, Im just a beginner. Do you have a formula for me to use. I think you have a good idea of what Im talking about. Yes, you are correct; the sets do not have to be randomly generated. I would just like to see the sets/ answer. I would like to have a formula where I could put in different numbers and get the results/ sets. For example, 40 numbers with 10 picked randomly or 40 employees with 10 random complaints which will result in 80% (8 out of 10) or more will end up in one of the three groups that belongs to a particular set. I would appreciate it if you can explain how to generate these results step by step. You can use a smaller version. For example, 12 employees with 3 complaints a month, after running formula, will result in 2 or more complaints ending up in one of the three groups that belong to a particular set. "Myrna Larson" wrote: I just wrote the code below to estimate the probability that 4 or 5 of 5 complaints come from the same group, assuming that that each person is equally likely to complain and that therefore the probability that a complaint comes from a given group is determined by its size. With 2 groups of size 7 and one of size 6, the result was approximately 13.9%. Am I correct that you are asking if there is a way to group the people such that EVERY MONTH, 80% or more of the complaints come from the same group? That would mean that an event that has only a ~14% chance of occurring happens every month. The chance of that happening for 3 months running is 0.14^3 = 0.002744, or about 3 in 1,000. IOW, your scenario is very unlikely UNLESS you have some real "complainers" among the 20 and you put them all into the same group (or the members of the group work in the same department and that department has real problems). i.e. the groups are NOT constructed randomly. You don't need a computer for that. Or am I missing the point entirely? ' Simulation of source of complaints ' There are 3 groups, of size 7, 7, and 6 ' There are 5 complaints per month, ' Calculate long-run probability that 4 or 5 of ' the 5 complaints all come from the same group Option Explicit Sub Complaints() Dim Four As Double Dim i As Long Dim j As Long Dim k As Long Dim NumTries As Long Dim S(1 To 3) As Long Dim T As Long Dim X As Double Randomize Timer NumTries = 1000000# For i = 1 To NumTries Erase S() For j = 1 To 5 '5 complaints per month X = Rnd() Select Case X 'determine group from which it came Case Is < 0.35: S(1) = S(1) + 1 'group 1 has 7/20 people = 35% Case Is < 0.70: S(2) = S(2) + 1 'group 2 has 7/20 people = 35% Case Else: S(3) = S(3) + 1 'the rest are in group 3 = 30% End Select Next j 'are there 4 or 5 in the same group? 'if so, count this as a "success" For j = 1 To 3 If S(j) = 4 Then Four = Four + 1 Exit For End If Next j Next i Debug.Print Format$(Four / NumTries, "0.00%") End Sub On Sat, 5 Mar 2005 00:53:02 -0800, "DeRizzio" wrote: I would like to create a macro that gives the different results for the following Question: I have 20 numbers (1-20) that are broken down into three groups. How can I create a macro that generates different sets of 3 groups of numbers, that when any 5 numbers are picked at random, at least four (80%) of the five numbers will be in a group? Each number is used once per set. The objective is to generate the least amount of sets to accomplish this goal. Partial Answer: Set 1 Group 1 = 1, 2, 3, 4, 5, 6, 7 Group 2 = 8, 9, 10, 11, 12, 13, 14 Group 3 = 15, 16, 17, 18, 19, 20 Set 2 Group 1 = 20, 14, 6, 18, 10, 12, 4 Group 2 = 16, 8, 2, 3, 7, 11, 15 Group 3 = 1, 5, 9, 11, 17, 19 Set 3 Group 1 = 3, 6, 9, 12, 15, 18 1 Group 2 = 4, 7, 10, 13, 16, 19, 5 Group 3 = 2, 8, 11, 14, 17, 20 And so on¦¦¦¦. In other words, Question: I have 20 employees that on average 5 of them will complain about different working conditions (or other problems) per month. Each employee will be listed/named 1-20 in excel. The 20 employees are broken down into 3 groups. How many different sets of 3 groups (numbered 1-20 where each number can only be used once per set) will I have to create to have at least 4 out of 5 random complaints end up in the same group? The objective is to accomplish this goal by using the least amount of sets. Can I create a macro for this? I'm using Microsoft Excel 2002. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Macro
AFAIK, there's no formula that you can use for this, and there is no
assignment to groups that will produce the results you describe. 1. Assuming that all employees are equally likely to complain, 2. ANY of the 3 groups could have 4 or 5 complaints, but that's more likely to happen with a group of 7 than a group of 6. 3. The chance that any group of 7 has 4 or 5 complaints (out of a total of 5) is only about 5.4%. 4. If #1 is true, the chance of having 80% or more of the complaints is the same regardless of which 7 people make up that group, i.e. it's the same for ANY possible group of 7. IOW, the only way to construct a group of 7 people who are more likely to complain than any other group is to identify the 7 biggest "complainers" and put them into the same group. The computer doesn't know who complains most unless you tell it. And "more likely" doesn't necessarily mean the complaints from that group will consistently be at least 80%. Unless, of course you have 4 people who ALWAYS complain every month. Then all you have to do is put them into the same group, and that group will ALWAYS have at least 4 complaints. BTW, have you calculated the number of different groups you can create by dividing 20 people into 2 groups of 7 and 1 of 6? =COMBIN(20,6) = 38760 different groups of 6 people Then splitting the remaining 14 into 2 groups, it's =COMBIN(14,7) = 3432. The product of those 2 numbers is 38760*3432 = 133,024,320 That's the total number of ways to divide the 20 people into 3 groups of sizes 6, 7, and 7. And it doesn't make thinks simpler to go to a group of 40 people and 10 complaints. To make 3 groups, sizes 13, 13, and 14, I am getting something like 2.4*10^17 different arrangements. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Macro
PS: What is your background in probability and statistics?
On Sat, 5 Mar 2005 18:33:03 -0800, "DeRizzio" wrote: Hello Myrna, Thank you for your assistance. However, Im still not sure how to generate these results/sets. When it comes to using VBE, Im just a beginner. Do you have a formula for me to use. I think you have a good idea of what Im talking about. Yes, you are correct; the sets do not have to be randomly generated. I would just like to see the sets/ answer. I would like to have a formula where I could put in different numbers and get the results/ sets. For example, 40 numbers with 10 picked randomly or 40 employees with 10 random complaints which will result in 80% (8 out of 10) or more will end up in one of the three groups that belongs to a particular set. I would appreciate it if you can explain how to generate these results step by step. You can use a smaller version. For example, 12 employees with 3 complaints a month, after running formula, will result in 2 or more complaints ending up in one of the three groups that belong to a particular set. "Myrna Larson" wrote: I just wrote the code below to estimate the probability that 4 or 5 of 5 complaints come from the same group, assuming that that each person is equally likely to complain and that therefore the probability that a complaint comes from a given group is determined by its size. With 2 groups of size 7 and one of size 6, the result was approximately 13.9%. Am I correct that you are asking if there is a way to group the people such that EVERY MONTH, 80% or more of the complaints come from the same group? That would mean that an event that has only a ~14% chance of occurring happens every month. The chance of that happening for 3 months running is 0.14^3 = 0.002744, or about 3 in 1,000. IOW, your scenario is very unlikely UNLESS you have some real "complainers" among the 20 and you put them all into the same group (or the members of the group work in the same department and that department has real problems). i.e. the groups are NOT constructed randomly. You don't need a computer for that. Or am I missing the point entirely? ' Simulation of source of complaints ' There are 3 groups, of size 7, 7, and 6 ' There are 5 complaints per month, ' Calculate long-run probability that 4 or 5 of ' the 5 complaints all come from the same group Option Explicit Sub Complaints() Dim Four As Double Dim i As Long Dim j As Long Dim k As Long Dim NumTries As Long Dim S(1 To 3) As Long Dim T As Long Dim X As Double Randomize Timer NumTries = 1000000# For i = 1 To NumTries Erase S() For j = 1 To 5 '5 complaints per month X = Rnd() Select Case X 'determine group from which it came Case Is < 0.35: S(1) = S(1) + 1 'group 1 has 7/20 people = 35% Case Is < 0.70: S(2) = S(2) + 1 'group 2 has 7/20 people = 35% Case Else: S(3) = S(3) + 1 'the rest are in group 3 = 30% End Select Next j 'are there 4 or 5 in the same group? 'if so, count this as a "success" For j = 1 To 3 If S(j) = 4 Then Four = Four + 1 Exit For End If Next j Next i Debug.Print Format$(Four / NumTries, "0.00%") End Sub On Sat, 5 Mar 2005 00:53:02 -0800, "DeRizzio" wrote: I would like to create a macro that gives the different results for the following Question: I have 20 numbers (1-20) that are broken down into three groups. How can I create a macro that generates different sets of 3 groups of numbers, that when any 5 numbers are picked at random, at least four (80%) of the five numbers will be in a group? Each number is used once per set. The objective is to generate the least amount of sets to accomplish this goal. Partial Answer: Set 1 Group 1 = 1, 2, 3, 4, 5, 6, 7 Group 2 = 8, 9, 10, 11, 12, 13, 14 Group 3 = 15, 16, 17, 18, 19, 20 Set 2 Group 1 = 20, 14, 6, 18, 10, 12, 4 Group 2 = 16, 8, 2, 3, 7, 11, 15 Group 3 = 1, 5, 9, 11, 17, 19 Set 3 Group 1 = 3, 6, 9, 12, 15, 18 1 Group 2 = 4, 7, 10, 13, 16, 19, 5 Group 3 = 2, 8, 11, 14, 17, 20 And so on . In other words, Question: I have 20 employees that on average 5 of them will complain about different working conditions (or other problems) per month. Each employee will be listed/named 1-20 in excel. The 20 employees are broken down into 3 groups. How many different sets of 3 groups (numbered 1-20 where each number can only be used once per set) will I have to create to have at least 4 out of 5 random complaints end up in the same group? The objective is to accomplish this goal by using the least amount of sets. Can I create a macro for this? I'm using Microsoft Excel 2002. Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Macro
More interesting, what is the name of the course you are talking or is this
some Lottery analysis? -- Regards, Tom Ogilvy "Myrna Larson" wrote in message ... PS: What is your background in probability and statistics? On Sat, 5 Mar 2005 18:33:03 -0800, "DeRizzio" wrote: Hello Myrna, Thank you for your assistance. However, I'm still not sure how to generate these results/sets. When it comes to using VBE, I'm just a beginner. Do you have a formula for me to use. I think you have a good idea of what I'm talking about. Yes, you are correct; the sets do not have to be randomly generated. I would just like to see the sets/ answer. I would like to have a formula where I could put in different numbers and get the results/ sets. For example, 40 numbers with 10 picked randomly or 40 employees with 10 random complaints which will result in 80% (8 out of 10) or more will end up in one of the three groups that belongs to a particular set. I would appreciate it if you can explain how to generate these results step by step. You can use a smaller version. For example, 12 employees with 3 complaints a month, after running formula, will result in 2 or more complaints ending up in one of the three groups that belong to a particular set. "Myrna Larson" wrote: I just wrote the code below to estimate the probability that 4 or 5 of 5 complaints come from the same group, assuming that that each person is equally likely to complain and that therefore the probability that a complaint comes from a given group is determined by its size. With 2 groups of size 7 and one of size 6, the result was approximately 13.9%. Am I correct that you are asking if there is a way to group the people such that EVERY MONTH, 80% or more of the complaints come from the same group? That would mean that an event that has only a ~14% chance of occurring happens every month. The chance of that happening for 3 months running is 0.14^3 = 0.002744, or about 3 in 1,000. IOW, your scenario is very unlikely UNLESS you have some real "complainers" among the 20 and you put them all into the same group (or the members of the group work in the same department and that department has real problems). i.e. the groups are NOT constructed randomly. You don't need a computer for that. Or am I missing the point entirely? ' Simulation of source of complaints ' There are 3 groups, of size 7, 7, and 6 ' There are 5 complaints per month, ' Calculate long-run probability that 4 or 5 of ' the 5 complaints all come from the same group Option Explicit Sub Complaints() Dim Four As Double Dim i As Long Dim j As Long Dim k As Long Dim NumTries As Long Dim S(1 To 3) As Long Dim T As Long Dim X As Double Randomize Timer NumTries = 1000000# For i = 1 To NumTries Erase S() For j = 1 To 5 '5 complaints per month X = Rnd() Select Case X 'determine group from which it came Case Is < 0.35: S(1) = S(1) + 1 'group 1 has 7/20 people = 35% Case Is < 0.70: S(2) = S(2) + 1 'group 2 has 7/20 people = 35% Case Else: S(3) = S(3) + 1 'the rest are in group 3 = 30% End Select Next j 'are there 4 or 5 in the same group? 'if so, count this as a "success" For j = 1 To 3 If S(j) = 4 Then Four = Four + 1 Exit For End If Next j Next i Debug.Print Format$(Four / NumTries, "0.00%") End Sub On Sat, 5 Mar 2005 00:53:02 -0800, "DeRizzio" wrote: I would like to create a macro that gives the different results for the following Question: I have 20 numbers (1-20) that are broken down into three groups. How can I create a macro that generates different sets of 3 groups of numbers, that when any 5 numbers are picked at random, at least four (80%) of the five numbers will be in a group? Each number is used once per set. The objective is to generate the least amount of sets to accomplish this goal. Partial Answer: Set 1 Group 1 = 1, 2, 3, 4, 5, 6, 7 Group 2 = 8, 9, 10, 11, 12, 13, 14 Group 3 = 15, 16, 17, 18, 19, 20 Set 2 Group 1 = 20, 14, 6, 18, 10, 12, 4 Group 2 = 16, 8, 2, 3, 7, 11, 15 Group 3 = 1, 5, 9, 11, 17, 19 Set 3 Group 1 = 3, 6, 9, 12, 15, 18 1 Group 2 = 4, 7, 10, 13, 16, 19, 5 Group 3 = 2, 8, 11, 14, 17, 20 And so on..... In other words, Question: I have 20 employees that on average 5 of them will complain about different working conditions (or other problems) per month. Each employee will be listed/named 1-20 in excel. The 20 employees are broken down into 3 groups. How many different sets of 3 groups (numbered 1-20 where each number can only be used once per set) will I have to create to have at least 4 out of 5 random complaints end up in the same group? The objective is to accomplish this goal by using the least amount of sets. Can I create a macro for this? I'm using Microsoft Excel 2002. Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Macro
Hello Myrna and Tom, Thank you for your response. I took QNT/530 which is
Statistics and Research Methods for Managerial Decisions. Yes, this can be used for lottery analysis. I was just trying something new but after Myrna's response, I have come to a dead end. This must be a similar/regular question for the support group. Do you have any other suggestions or should I just keep my day job and stop thinking about these break-through ideas? "Tom Ogilvy" wrote: More interesting, what is the name of the course you are talking or is this some Lottery analysis? -- Regards, Tom Ogilvy "Myrna Larson" wrote in message ... PS: What is your background in probability and statistics? On Sat, 5 Mar 2005 18:33:03 -0800, "DeRizzio" wrote: Hello Myrna, Thank you for your assistance. However, I'm still not sure how to generate these results/sets. When it comes to using VBE, I'm just a beginner. Do you have a formula for me to use. I think you have a good idea of what I'm talking about. Yes, you are correct; the sets do not have to be randomly generated. I would just like to see the sets/ answer. I would like to have a formula where I could put in different numbers and get the results/ sets. For example, 40 numbers with 10 picked randomly or 40 employees with 10 random complaints which will result in 80% (8 out of 10) or more will end up in one of the three groups that belongs to a particular set. I would appreciate it if you can explain how to generate these results step by step. You can use a smaller version. For example, 12 employees with 3 complaints a month, after running formula, will result in 2 or more complaints ending up in one of the three groups that belong to a particular set. "Myrna Larson" wrote: I just wrote the code below to estimate the probability that 4 or 5 of 5 complaints come from the same group, assuming that that each person is equally likely to complain and that therefore the probability that a complaint comes from a given group is determined by its size. With 2 groups of size 7 and one of size 6, the result was approximately 13.9%. Am I correct that you are asking if there is a way to group the people such that EVERY MONTH, 80% or more of the complaints come from the same group? That would mean that an event that has only a ~14% chance of occurring happens every month. The chance of that happening for 3 months running is 0.14^3 = 0.002744, or about 3 in 1,000. IOW, your scenario is very unlikely UNLESS you have some real "complainers" among the 20 and you put them all into the same group (or the members of the group work in the same department and that department has real problems). i.e. the groups are NOT constructed randomly. You don't need a computer for that. Or am I missing the point entirely? ' Simulation of source of complaints ' There are 3 groups, of size 7, 7, and 6 ' There are 5 complaints per month, ' Calculate long-run probability that 4 or 5 of ' the 5 complaints all come from the same group Option Explicit Sub Complaints() Dim Four As Double Dim i As Long Dim j As Long Dim k As Long Dim NumTries As Long Dim S(1 To 3) As Long Dim T As Long Dim X As Double Randomize Timer NumTries = 1000000# For i = 1 To NumTries Erase S() For j = 1 To 5 '5 complaints per month X = Rnd() Select Case X 'determine group from which it came Case Is < 0.35: S(1) = S(1) + 1 'group 1 has 7/20 people = 35% Case Is < 0.70: S(2) = S(2) + 1 'group 2 has 7/20 people = 35% Case Else: S(3) = S(3) + 1 'the rest are in group 3 = 30% End Select Next j 'are there 4 or 5 in the same group? 'if so, count this as a "success" For j = 1 To 3 If S(j) = 4 Then Four = Four + 1 Exit For End If Next j Next i Debug.Print Format$(Four / NumTries, "0.00%") End Sub On Sat, 5 Mar 2005 00:53:02 -0800, "DeRizzio" wrote: I would like to create a macro that gives the different results for the following Question: I have 20 numbers (1-20) that are broken down into three groups. How can I create a macro that generates different sets of 3 groups of numbers, that when any 5 numbers are picked at random, at least four (80%) of the five numbers will be in a group? Each number is used once per set. The objective is to generate the least amount of sets to accomplish this goal. Partial Answer: Set 1 Group 1 = 1, 2, 3, 4, 5, 6, 7 Group 2 = 8, 9, 10, 11, 12, 13, 14 Group 3 = 15, 16, 17, 18, 19, 20 Set 2 Group 1 = 20, 14, 6, 18, 10, 12, 4 Group 2 = 16, 8, 2, 3, 7, 11, 15 Group 3 = 1, 5, 9, 11, 17, 19 Set 3 Group 1 = 3, 6, 9, 12, 15, 18 1 Group 2 = 4, 7, 10, 13, 16, 19, 5 Group 3 = 2, 8, 11, 14, 17, 20 And so on..... In other words, Question: I have 20 employees that on average 5 of them will complain about different working conditions (or other problems) per month. Each employee will be listed/named 1-20 in excel. The 20 employees are broken down into 3 groups. How many different sets of 3 groups (numbered 1-20 where each number can only be used once per set) will I have to create to have at least 4 out of 5 random complaints end up in the same group? The objective is to accomplish this goal by using the least amount of sets. Can I create a macro for this? I'm using Microsoft Excel 2002. Thanks |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Macro
I believe in your original question, you talked about "random complaints".
That means that WRT to their probability of complaining, the people are identical. Let's say you have 21 people and you line them up in a row. A complaint comes from one of them. The chance it came from the 1st person is 1/21; the chance it's from the 2nd is 1/21. For each person, that chance is the same, 1/21. Now let's divide the row into segments of 7 people each. The chance the complaint comes from the first segment is 7/21; from the 2nd segment, 7/21, and from the 3rd segment, 7/21. The chances are equal because the segments consist of an equal number of identical members. And there's no way that rearranging the people will change that. How could it? The people are identical. The only way that can be changed is if the identity of the 1st complainer affects who complains next. If that's the case, then the complaints are not random. In short, keep your day job. Instead of thinking about "break-through" ideas, go back to your statistics text book <vbg. On Sun, 6 Mar 2005 23:05:52 -0800, "DeRizzio" wrote: Hello Myrna and Tom, Thank you for your response. I took QNT/530 which is Statistics and Research Methods for Managerial Decisions. Yes, this can be used for lottery analysis. I was just trying something new but after Myrna's response, I have come to a dead end. This must be a similar/regular question for the support group. Do you have any other suggestions or should I just keep my day job and stop thinking about these break-through ideas? "Tom Ogilvy" wrote: More interesting, what is the name of the course you are talking or is this some Lottery analysis? -- Regards, Tom Ogilvy "Myrna Larson" wrote in message ... PS: What is your background in probability and statistics? On Sat, 5 Mar 2005 18:33:03 -0800, "DeRizzio" wrote: Hello Myrna, Thank you for your assistance. However, I'm still not sure how to generate these results/sets. When it comes to using VBE, I'm just a beginner. Do you have a formula for me to use. I think you have a good idea of what I'm talking about. Yes, you are correct; the sets do not have to be randomly generated. I would just like to see the sets/ answer. I would like to have a formula where I could put in different numbers and get the results/ sets. For example, 40 numbers with 10 picked randomly or 40 employees with 10 random complaints which will result in 80% (8 out of 10) or more will end up in one of the three groups that belongs to a particular set. I would appreciate it if you can explain how to generate these results step by step. You can use a smaller version. For example, 12 employees with 3 complaints a month, after running formula, will result in 2 or more complaints ending up in one of the three groups that belong to a particular set. "Myrna Larson" wrote: I just wrote the code below to estimate the probability that 4 or 5 of 5 complaints come from the same group, assuming that that each person is equally likely to complain and that therefore the probability that a complaint comes from a given group is determined by its size. With 2 groups of size 7 and one of size 6, the result was approximately 13.9%. Am I correct that you are asking if there is a way to group the people such that EVERY MONTH, 80% or more of the complaints come from the same group? That would mean that an event that has only a ~14% chance of occurring happens every month. The chance of that happening for 3 months running is 0.14^3 = 0.002744, or about 3 in 1,000. IOW, your scenario is very unlikely UNLESS you have some real "complainers" among the 20 and you put them all into the same group (or the members of the group work in the same department and that department has real problems). i.e. the groups are NOT constructed randomly. You don't need a computer for that. Or am I missing the point entirely? ' Simulation of source of complaints ' There are 3 groups, of size 7, 7, and 6 ' There are 5 complaints per month, ' Calculate long-run probability that 4 or 5 of ' the 5 complaints all come from the same group Option Explicit Sub Complaints() Dim Four As Double Dim i As Long Dim j As Long Dim k As Long Dim NumTries As Long Dim S(1 To 3) As Long Dim T As Long Dim X As Double Randomize Timer NumTries = 1000000# For i = 1 To NumTries Erase S() For j = 1 To 5 '5 complaints per month X = Rnd() Select Case X 'determine group from which it came Case Is < 0.35: S(1) = S(1) + 1 'group 1 has 7/20 people = 35% Case Is < 0.70: S(2) = S(2) + 1 'group 2 has 7/20 people = 35% Case Else: S(3) = S(3) + 1 'the rest are in group 3 = 30% End Select Next j 'are there 4 or 5 in the same group? 'if so, count this as a "success" For j = 1 To 3 If S(j) = 4 Then Four = Four + 1 Exit For End If Next j Next i Debug.Print Format$(Four / NumTries, "0.00%") End Sub On Sat, 5 Mar 2005 00:53:02 -0800, "DeRizzio" wrote: I would like to create a macro that gives the different results for the following Question: I have 20 numbers (1-20) that are broken down into three groups. How can I create a macro that generates different sets of 3 groups of numbers, that when any 5 numbers are picked at random, at least four (80%) of the five numbers will be in a group? Each number is used once per set. The objective is to generate the least amount of sets to accomplish this goal. Partial Answer: Set 1 Group 1 = 1, 2, 3, 4, 5, 6, 7 Group 2 = 8, 9, 10, 11, 12, 13, 14 Group 3 = 15, 16, 17, 18, 19, 20 Set 2 Group 1 = 20, 14, 6, 18, 10, 12, 4 Group 2 = 16, 8, 2, 3, 7, 11, 15 Group 3 = 1, 5, 9, 11, 17, 19 Set 3 Group 1 = 3, 6, 9, 12, 15, 18 1 Group 2 = 4, 7, 10, 13, 16, 19, 5 Group 3 = 2, 8, 11, 14, 17, 20 And so on..... In other words, Question: I have 20 employees that on average 5 of them will complain about different working conditions (or other problems) per month. Each employee will be listed/named 1-20 in excel. The 20 employees are broken down into 3 groups. How many different sets of 3 groups (numbered 1-20 where each number can only be used once per set) will I have to create to have at least 4 out of 5 random complaints end up in the same group? The objective is to accomplish this goal by using the least amount of sets. Can I create a macro for this? I'm using Microsoft Excel 2002. Thanks |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Macro
Have you looked at the factorial function in excel to provide an idea
of how many sets you will get. FACT() For the math see: http://mathworld.wolfram.com/Combination.html The number of ways of picking k unordered outcomes from n possibilities where n = 20 and k=5 n!/k!(n-k)! fact(20)/fact(5)*fact(20-5) Number of possible outcomes 15504 In the equation ! exclamation means factorial. Nancy Moon On Sat, 5 Mar 2005 00:53:02 -0800, "DeRizzio" wrote: I would like to create a macro that gives the different results for the following Question: I have 20 numbers (1-20) that are broken down into three groups. How can I create a macro that generates different sets of 3 groups of numbers, that when any 5 numbers are picked at random, at least four (80%) of the five numbers will be in a group? Each number is used once per set. The objective is to generate the least amount of sets to accomplish this goal. Partial Answer: Set 1 Group 1 = 1, 2, 3, 4, 5, 6, 7 Group 2 = 8, 9, 10, 11, 12, 13, 14 Group 3 = 15, 16, 17, 18, 19, 20 Set 2 Group 1 = 20, 14, 6, 18, 10, 12, 4 Group 2 = 16, 8, 2, 3, 7, 11, 15 Group 3 = 1, 5, 9, 11, 17, 19 Set 3 Group 1 = 3, 6, 9, 12, 15, 18 1 Group 2 = 4, 7, 10, 13, 16, 19, 5 Group 3 = 2, 8, 11, 14, 17, 20 And so on . In other words, Question: I have 20 employees that on average 5 of them will complain about different working conditions (or other problems) per month. Each employee will be listed/named 1-20 in excel. The 20 employees are broken down into 3 groups. How many different sets of 3 groups (numbered 1-20 where each number can only be used once per set) will I have to create to have at least 4 out of 5 random complaints end up in the same group? The objective is to accomplish this goal by using the least amount of sets. Can I create a macro for this? I'm using Microsoft Excel 2002. Thanks ___________________ |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Macro
Or use the combinations function directly
=combin(20,5) as expected, also gives15504 -- Regards, Tom Ogilvy "Nancy Moon" wrote in message ... Have you looked at the factorial function in excel to provide an idea of how many sets you will get. FACT() For the math see: http://mathworld.wolfram.com/Combination.html The number of ways of picking k unordered outcomes from n possibilities where n = 20 and k=5 n!/k!(n-k)! fact(20)/fact(5)*fact(20-5) Number of possible outcomes 15504 In the equation ! exclamation means factorial. Nancy Moon On Sat, 5 Mar 2005 00:53:02 -0800, "DeRizzio" wrote: I would like to create a macro that gives the different results for the following Question: I have 20 numbers (1-20) that are broken down into three groups. How can I create a macro that generates different sets of 3 groups of numbers, that when any 5 numbers are picked at random, at least four (80%) of the five numbers will be in a group? Each number is used once per set. The objective is to generate the least amount of sets to accomplish this goal. Partial Answer: Set 1 Group 1 = 1, 2, 3, 4, 5, 6, 7 Group 2 = 8, 9, 10, 11, 12, 13, 14 Group 3 = 15, 16, 17, 18, 19, 20 Set 2 Group 1 = 20, 14, 6, 18, 10, 12, 4 Group 2 = 16, 8, 2, 3, 7, 11, 15 Group 3 = 1, 5, 9, 11, 17, 19 Set 3 Group 1 = 3, 6, 9, 12, 15, 18 1 Group 2 = 4, 7, 10, 13, 16, 19, 5 Group 3 = 2, 8, 11, 14, 17, 20 And so on..... In other words, Question: I have 20 employees that on average 5 of them will complain about different working conditions (or other problems) per month. Each employee will be listed/named 1-20 in excel. The 20 employees are broken down into 3 groups. How many different sets of 3 groups (numbered 1-20 where each number can only be used once per set) will I have to create to have at least 4 out of 5 random complaints end up in the same group? The objective is to accomplish this goal by using the least amount of sets. Can I create a macro for this? I'm using Microsoft Excel 2002. Thanks ___________________ |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Macro
Of course, this formula does not describe his problem precisely, but gives
some insight into magnitudes. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Or use the combinations function directly =combin(20,5) as expected, also gives15504 -- Regards, Tom Ogilvy "Nancy Moon" wrote in message ... Have you looked at the factorial function in excel to provide an idea of how many sets you will get. FACT() For the math see: http://mathworld.wolfram.com/Combination.html The number of ways of picking k unordered outcomes from n possibilities where n = 20 and k=5 n!/k!(n-k)! fact(20)/fact(5)*fact(20-5) Number of possible outcomes 15504 In the equation ! exclamation means factorial. Nancy Moon On Sat, 5 Mar 2005 00:53:02 -0800, "DeRizzio" wrote: I would like to create a macro that gives the different results for the following Question: I have 20 numbers (1-20) that are broken down into three groups. How can I create a macro that generates different sets of 3 groups of numbers, that when any 5 numbers are picked at random, at least four (80%) of the five numbers will be in a group? Each number is used once per set. The objective is to generate the least amount of sets to accomplish this goal. Partial Answer: Set 1 Group 1 = 1, 2, 3, 4, 5, 6, 7 Group 2 = 8, 9, 10, 11, 12, 13, 14 Group 3 = 15, 16, 17, 18, 19, 20 Set 2 Group 1 = 20, 14, 6, 18, 10, 12, 4 Group 2 = 16, 8, 2, 3, 7, 11, 15 Group 3 = 1, 5, 9, 11, 17, 19 Set 3 Group 1 = 3, 6, 9, 12, 15, 18 1 Group 2 = 4, 7, 10, 13, 16, 19, 5 Group 3 = 2, 8, 11, 14, 17, 20 And so on..... In other words, Question: I have 20 employees that on average 5 of them will complain about different working conditions (or other problems) per month. Each employee will be listed/named 1-20 in excel. The 20 employees are broken down into 3 groups. How many different sets of 3 groups (numbered 1-20 where each number can only be used once per set) will I have to create to have at least 4 out of 5 random complaints end up in the same group? The objective is to accomplish this goal by using the least amount of sets. Can I create a macro for this? I'm using Microsoft Excel 2002. Thanks ___________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a New Macro | Excel Discussion (Misc queries) | |||
Macro Creating | New Users to Excel | |||
help creating a macro | Excel Programming | |||
help creating a macro | Excel Programming | |||
Help Creating a Macro | Excel Programming |