Creating a Macro

[DeRizzio]

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

 

[Myrna Larson]

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

 

[DeRizzio]

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]

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.

 

[Myrna Larson]

PS: What is your background in probability and statistics?

 

[Tom Ogilvy]

More interesting, what is the name of the course you are talking or is this
some Lottery analysis?

 

[DeRizzio]

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?

 

[Myrna Larson]

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>.

 

[Tom Ogilvy]

Or use the combinations function directly

=combin(20,5)

as expected, also gives15504

 

[Tom Ogilvy]

Of course, this formula does not describe his problem precisely, but gives
some insight into magnitudes.