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