Statistics Problem

[iknowu99]

Deal with one gender first, due to the parallels and independence.

1)

The total: AAA can fit into 13 >> 13choose3 ways or 13C3

total ways AAA can go into a 'six group' is 6choose3 or 6C3=20

These guys can also go to Group B. There they can fit 7C3 or 35 different ways.

multiply that by 4 since A or B or C or D can be grouped
4 * (6C3 + 7C3) / 13C3

this is when team members from at least one team are joined again on a group

 

[iknowu99]

2)

When you capitalize AND in math it refers to independent events and the two events must be multiplied

X=(7C3+6C3)/13C3

X squared is the case when two teams, for example AAA and BBB are fit inside the 13 slots. both teams are grouped.


4X-(X^2 + X^3 + X^4) square this for both males and females = .5238413672

 

[iknowu99]

i learned i made a mistake, the following is done with vba and iterations

Book1.xls
	A	B	C	D	E	F	G	H	I	J
1	A
2	B		A	D	B	D	C	B
3	C
4	D
5	E
6	A		E	A	D	D	C	C	B
7	B
8	C
9	D									iterations
10	A									145403
11	B
12	C
13	D
14			no teams or groups			68550	0.471448
15			1 team to group			45596	0.313584
16			2 teams to group or groups			27684	0.190395
17			3 teams to group or groups			3035	0.020873
18			4 teams to group or groops			538	0.0037
19
20
Sheet2

Option Explicit

Sub CombinationsCHECK()

Dim i%
Dim x%
Dim iterations As Double
Dim string6word$
Dim string6$, string7word$

Dim PatternMatch6%, PatternMatch7%

'Application.ScreenUpdating = False


For iterations = 1 To 1000000
        
    Range("A1:A13").Font.ColorIndex = 0


'6
    For x = 1 To 6
gotohere:
    i = Int((13) * Rnd + 1)
    
    If Cells(i, 1).Font.ColorIndex<> 3 Then
    Cells(2, 2 + x) = Cells(i, 1)
    Cells(i, 1).Font.ColorIndex = 3
    Else: GoTo gotohere
    End If
    
    Next x



For x = 1 To 6
string6word = string6word & Cells(2, 2 + x)
Next x

If Len(string6word) - Len(Replace(string6word, "A", "")) = 3 Then PatternMatch6 = 1 + PatternMatch6
If Len(string6word) - Len(Replace(string6word, "B", "")) = 3 Then PatternMatch6 = 1 + PatternMatch6
If Len(string6word) - Len(Replace(string6word, "C", "")) = 3 Then PatternMatch6 = 1 + PatternMatch6
If Len(string6word) - Len(Replace(string6word, "D", "")) = 3 Then PatternMatch6 = 1 + PatternMatch6

'Cells(15, 5) = Cells(15, 5) + 1





'7
    For x = 1 To 7
gotohere2:
    i = Int((13) * Rnd + 1)
    
    If Cells(i, 1).Font.ColorIndex<> 3 Then
    Cells(6, 2 + x) = Cells(i, 1)
    Cells(i, 1).Font.ColorIndex = 3
    Else: GoTo gotohere2
    End If
    
    Next x


For x = 1 To 7
string7word = string7word & Cells(6, 2 + x)
Next x

If Len(string7word) - Len(Replace(string7word, "A", "")) = 3 Then PatternMatch7 = 1 + PatternMatch7
If Len(string7word) - Len(Replace(string7word, "B", "")) = 3 Then PatternMatch7 = 1 + PatternMatch7
If Len(string7word) - Len(Replace(string7word, "C", "")) = 3 Then PatternMatch7 = 1 + PatternMatch7
If Len(string7word) - Len(Replace(string7word, "D", "")) = 3 Then PatternMatch7 = 1 + PatternMatch7

'Cells(15, 6) = Cells(15, 6) + 1



If PatternMatch7 + PatternMatch6 = 0 Then Cells(14, 6) = Cells(14, 6) + 1

If PatternMatch7 + PatternMatch6 = 1 Then Cells(15, 6) = Cells(15, 6) + 1

If PatternMatch7 + PatternMatch6 = 2 Then Cells(16, 6) = Cells(16, 6) + 1

If PatternMatch7 + PatternMatch6 = 3 Then Cells(17, 6) = Cells(17, 6) + 1

If PatternMatch7 + PatternMatch6 = 4 Then Cells(18, 6) = Cells(18, 6) + 1

If PatternMatch7 + PatternMatch6 = 5 Then
Cells(19, 6) = Cells(19, 6) + 1
End If




Cells(10, 10) = iterations



PatternMatch6 = 0
PatternMatch7 = 0
string6word = ""
string7word = ""
    Range("A1:A13").Font.ColorIndex = 0
Next iterations


End Sub

 

[Bob Rooney]

I have a little statistics problem I want to figure out, and I was hoping some people here may be able to help. After seeing all the good responses to the Birthday Problem thread, I figure someone might be able to help me.


You have 5 teams, broken up as follows:

Team A - 3 males, 3 females
Team B - 3 males, 3 females
Team C - 3 males, 3 females
Team D - 3 males, 3 females
Team E - 1 male, 1 females


There will be 4 groups formed at random from these teams:

Group A - 6 males
Group B - 7 males
Group C - 6 females
Group D - 7 females


I have two questions based on this:

(1) What is the likelihood of all 3 males (or all 3 females) from one team all ending up in the same group?

ex. All 3 males from Team A end up in Group A

(2) What is the likelihood that all 3 males from one team will end up in one group AND all 3 females from one team will end up in one group, while no other team placing either 3 males or 3 females in one group?

ex. All 3 males from Team A end up in Group A
All 3 females from Team A end up in Group C
All other teams are split up


I hope I worded the second question well, if you need any clarification just let me know. Thanks in advance for any answer you can give.

For the first question only at this time. I hope I understood it to give you the correct answer.

Probability of all from one team together in the regrouping...

Team of 6 males/females:

COMBIN(3,3)*COMBIN(10,3) / COMBIN(13,6) = 120/1716 = .06993

Team of 7 males/females:

COMBIN(3,3)*COMBIN(10,4) / COMBIN(13,7) = 216/1716 = .12238

However, each of these must be multiplied by 4 to get the total possibility for ANY team to have all their members/gender selected for the regrouped team.

I have ignored the Team E option, because the all members to one group condition clearly applies.

PLEASE NOTE: Excel has a built-in function (Analysis ToolPak?) that calculates this directly
HYPGEOMDIST(...)

I will look at this in more detail and also the second part.

 

[Bob Rooney]

I am getting a different answer.

Probability of a regrouped team having all members from an initial group equals 1 - Prob members are split.

For the members to be split, that means that each initial team has either 1 or 2 members chosen (cannot be 0 or 3), except for Team E, of course.

10 possible scenarios can occur, each equally likely.

P(no group intact) = 47.2% --> P(at least one group intact) = 52.8%.

Book1
	A	B	C	D	E	F	G	H
1	Option with no team having all members on a reselected team of 6:
2	Team	A	B	C	D	E
3	Total Per Team	3	3	3	3	1
4
5	I	1	1	1	2	1		4.72%
6	I	1	1	2	1	1		4.72%
7	III	1	2	1	1	1		4.72%
8	IV	2	1	1	1	1		4.72%
9	V	1	1	2	2	0		4.72%
10	VI	1	2	2	1	0		4.72%
11	VII	2	2	1	1	0		4.72%
12	VIII	2	1	1	2	0		4.72%
13	IX	1	2	1	2	0		4.72%
14	X	2	1	2	1	0		4.72%
15
16								47.20%
Sheet1

 

[Bob Rooney]

OOPS. The second table did not come through. The probabilities with 7 equal the probabilities with 6 members to a team.

Somewhere my mathematics is incorrect. Will try to determine where it is wrong.

 

[iknowu99]

Hi Bob, I agree with your 47.2 calculation. From how i understand the problem the simulation i ran is close to the answer yet i can not prove this with math; at least not yet

what i was tryin earlier: 6choose3 is when 3 A's try to make it to the 6 group, there are 20 ways they can fit to the 6 group; to the 7 group 7c3, so 35 ways. When 3 A's try to make it to the other side they have 13c3 ways to do this.

On a general note multiplying events means these events will happen at the same time. So my suggested answers 1) ~31.3584%. 2) ~9.8335%

 

[Bob Rooney]

This has to have an exact answer that is more straightforward than this...

In A1:E1, type the A, B, C, D, E category headings.
In A2:E2, type 3,3,3,3,1

Preselect A3:E86, go to the formula bar and type in
=Numer(6,A2,B2,C2,D2,E2)

6 is the total selected per the new group.

Confirm with Ctrl-Shift-Enter to array enter results.

The Numer UDF is

Function Numer(TotSel As Long, A As Long, B As Long, C As Long, D As Long, E As Long)

Dim v As Long, w As Long, x As Long, y As Long, z As Long
Dim Cnt As Long

Dim arrList
For v = 0 To A
    For w = 0 To B
        For x = 0 To C
            For y = 0 To D
                For z = 0 To E
                    If v + w + x + y + z = TotSel Then
                        Cnt = Cnt + 1
                        If Cnt = 1 Then
                            ReDim arrList(1 To 5, 1 To Cnt) As Long
                        Else
                            ReDim Preserve arrList(1 To 5, 1 To Cnt) As Long
                        End If
                        arrList(1, Cnt) = v
                        arrList(2, Cnt) = w
                        arrList(3, Cnt) = x
                        arrList(4, Cnt) = y
                        arrList(5, Cnt) = z
                    End If
                Next z
            Next y
        Next x
    Next w
Next v

Numer = Application.Transpose(arrList)
End Function

The Numer UDF exhausts all legal possible successful outcomes and lists them on the worksheet.

In F3, array-enter the following formula:
=PRODUCT(COMBIN($A$2:$E$2,A3:E3))

Copy down to F86.

In G3, enter
=IF(OR(COUNTIF(A3:D3,3)<>0,COUNTIF(A3:D3,0)<>0),1,0)
and copy down to G86. No need to check column E, as that is the single member E group.

In H3, enter the following formula =SUMPRODUCT(F3:F86,G3:G86)

I got 906. This is the numerator to the equation. The denominator equals =COMBIN(13,6) = 1716.

The probability of 3 members from the same team land in the group of 6 or 7 is 906/1716 or 52.8%.

We only need to use the new group of 6 members. Testing if 3 members in the new group occur AND testing if zero members occur in the new group -- indicating all 3 went to the new group with 7 elements.

This is the same as earlier, 1 - P(1 or 2).

Still do not understand fully why the HYPGEOMDIST option does not work.

 

[pgc01]

Hi

This is a problem of Combinatorics like the ones I (vaguely) remember doing at school.

I solved it analytically (as far as I can remember how one did it). I got the same result as Bob.

Book1.xls
	A	B	C	D	E	F
1
2			Totalnumberofpossiblegroupsof6people(Combin(13,6):		1716
3
4			Thereare5possiblecases:
5					Total
6	Case1		NoneoftheteamsA,B,CandDhaveallitsmemberstogetherineitherG6orG7		810
7	Case2		Thereis1andonly1teamA,B,CorDthathasallitsmemberstogetherineitherG6orG7		540
8	Case3		Thereare2andonly2oftheteamsA,B,CorDthathaveallitsmemberstogetherinG6orG7		324
9	Case4		Thereare3andonly3oftheteamsA,B,CorDthathaveallitsmemberstogetherinG6orG7		36
10	Case5		Allthe4teamsA,B,CandDhaveallitsmemberstogetherinG6orG7		6
11
GUI

(1) What is the likelihood of all 3 males (or all 3 females) from one team all ending up in the same group?

Likelihood of all 3 males from one team all ending up in the same group or likelihood of all 3 females from one team all ending up in the same group:

(Case2 + Case 3 + Case 4 + Case 5) / Total = 906 / 1712 = 52.8 %

or

(Total - Case 1) / Total = 52.8%

Likelihood of all 3 males (or all 3 females) from one team all ending up in the same group:

1-( Case 1 / Total) * ( Case 1 / Total) = 77.7%

(2) What is the likelihood that all 3 males from one team will end up in one group AND all 3 females from one team will end up in one group, while no other team placing either 3 males or 3 females in one group?

Since the events are independent

(Case 2 / Total) * (Case 2 / Total) = 9.9 %

Cheers
PGC

P. S. Analytical resolution:

- I will call the final group of 6 people G6 and the final group of seven people G7.
- I will only use the group of 6 people to do the calculations, since the group of 7 people adds no information.
- I will use the numbers 1, 2, 3 and 4 to represent an element of one of the teams A, B, C D and the letter E to represent the element in group E

Case 1 - None of the teams A, B, C and D have all its members together in either G6 or G7

There are 2 possible configurations of G6

112234: Combin(4,2)*3*3*3*3=486
11234E: 4*3*3*3*3=324

Total for Case 1: 486+324 = 810

Case 2 - There is 1 and only 1 team A, B, C or D that has all its members together in either G6 or G7

There are 3 possible configurations of G6

111234: 4*3*3*3=108
112233: Combin(4,3)*3*3*3=108
11223E: Combin(4,2)*3*3*2*3=324

Total for Case 2: 108+108+324 = 540

Case 3 - There are 2 and only 2 of the teams A, B, C or D that have all its members together in G6 or G7

There are 2 possible configurations of G6

111223: 4*3*3*2*3=216
11123E: 4*Combin(3,2)*3*3=108

Total for Case 3: 216+108 = 324

Case 4 - There are 3 and only 3 of the teams A, B, C or D that have all its members together in G6 or G7

There is 1 possible configuration of G6

11122E: 4*3*3=36

Total for Case 4: 36

Case 5 - All the 4 teams A, B, C and D have all its members together in G6 or G7

There is 1 possible configuration of G6

111222: Combin(4,2)=6

Total for Case 5: 6

 

[pgc01]

Curious.

I compared my values with the ones in the table that iknowu99 posted and they are very (very) similar but not exactly equal.

I wonder why such small differences?

Book1.xls
	A	B	C	D	E	F	G	H
1
2					iknowu99		PGC
3
4	Case1		NoneoftheteamsA,B,CandDhaveallitsmemberstogetherineitherG6orG7		0.471448		0.472028
5	Case2		Thereis1andonly1teamA,B,CorDthathasallitsmemberstogetherineitherG6orG7		0.313584		0.314685
6	Case3		Thereare2andonly2oftheteamsA,B,CorDthathaveallitsmemberstogetherinG6orG7		0.190395		0.188811
7	Case4		Thereare3andonly3oftheteamsA,B,CorDthathaveallitsmemberstogetherinG6orG7		0.020873		0.020979
8	Case5		Allthe4teamsA,B,CandDhaveallitsmemberstogetherinG6orG7		0.003700		0.003497
9
GUI