What did I do wrong in this probability sheet?

[JenniferMurphy]

While playing solitaire, I was curious about the odds of 1 or more aces among the 7 initial face-up cards. I created the little table in the mini-sheet below. I then had the solitaire game deal 100 hands and tallied the number of aces in the initial face up cards. That tally is in row 6. Row 7 has what I thought were the expected odds, but there must be an error. They do not add up to 100 (I7 highlighted in yellow). Row 8 has the actual percentages and row 9 has the percentage error.

Can someone tell me where I went wrong? Thanks

Ace Odds.xlsx
	B	C	D	E	F	G	H	I
5	#Aces	0 Aces	1 Ace	2 Aces	3 Aces	4 Aces	1-4 Aces	Totals
6	Deals	49	41	10	0	0	51	100
7	Prob	57.1038%	33.3106%	8.3276%	1.1566%	0.0964%	42.8912%	99.9950%
8	Actual	49.0000%	41.0000%	10.0000%	0.0000%	0.0000%	51.0000%	100.0000%
9	% Error	-14.1914%	+23.0841%	+20.0820%	-100.0000%	-100.0000%	-1.568339
10	Combin	7,7 = 1	7,1 = 7	7,2 = 21	7,3 = 35	7,4 = 35
Aces

Cell Formulas
Range	Formula
H6:H9	H6	=SUM(D6:G6)
I6:I8	I6	=SUM(C6:G6)
C7	C7	=COMBIN(7,7) * ((4/52)^0) * ((48/52)^7)
D7	D7	=COMBIN(7,1) * ((4/52)^1) * ((48/52)^6)
E7	E7	=COMBIN(7,2) * ((4/52)^2) * ((48/52)^5)
F7	F7	=COMBIN(7,3) * ((4/52)^3) * ((48/52)^4)
G7	G7	=COMBIN(7,4) * ((4/52)^4) * ((48/52)^3)
C8	C8	=C6/I6
D8	D8	=D6/I6
E8	E8	=E6/I6
F8	F8	=F6/I6
G8	G8	=G6/I6
C9:G9	C9	=C8/C7 - 1
C10	C10	="7,7 = " & COMBIN(7,7)
D10	D10	="7,1 = " & COMBIN(7,1)
E10	E10	="7,2 = " & COMBIN(7,2)
F10	F10	="7,3 = " & COMBIN(7,3)
G10	G10	="7,4 = " & COMBIN(7,4)

 

[Sulprobil]

Hello,

Delete C7:G7 and write into C7:

=COMBIN(4,SEQUENCE(1,5,0,1))*COMBIN(48,7-SEQUENCE(1,5,0,1))/COMBIN(52,7)

Regards,
Bernd

 

[JenniferMurphy]

Hello,

Delete C7:G7 and write into C7:

=COMBIN(4,SEQUENCE(1,5,0,1))*COMBIN(48,7-SEQUENCE(1,5,0,1))/COMBIN(52,7)

Regards,
Bernd

Wow! I get the correct formula and a more compact expression! Very nice. Thank you.

I think I see why your formula works. It divides the product of the number of aces times the number of non-aces by the possible deals. Correct?

But I don't see why mine fails. The expression I had for 1 ace, =COMBIN(7,1) * ((4/52)^1) * ((48/52)^6), multiplies the odds of 1 ace times the odds of 6 non-aces ties the number of combinations. Any help there?

Thanks

 

[Sulprobil]

Hello,

Yes, you correctly understand my formula, I think.
Your formulas calculate the probabilities with replacement as you can simulate with this VBA:

Sub monte()
Const runs = 100000
Dim i As Long, j As Long, n As Long, r(1 To 5) As Long
With Application.WorksheetFunction
Randomize
For i = 1 To runs
    n = 0
    For j = 1 To 7
        If .RandBetween(1, 52) < 5 Then
            n = n + 1
            If n = 4 Then Exit For
        End If
    Next j
    r(1 + n) = r(1 + n) + 1
Next i
Cells(11, 2).Formula = r(1) / runs
Cells(11, 3).Formula = r(2) / runs
Cells(11, 4).Formula = r(3) / runs
Cells(11, 5).Formula = r(4) / runs
Cells(11, 6).Formula = r(5) / runs
End With
End Sub

The correct probabilities without replacement you get if you change the .RandBetween row to

If .RandBetween(1, 53 - j) < 5 - n Then

Note: Start above simulation with a smaller number of runs (1000 or 10000) if your computer is a bit slow. But then the simulation will be less accurate.

Regards,
Bernd

 

[JenniferMurphy]

Hello,

Yes, you correctly understand my formula, I think.
Your formulas calculate the probabilities with replacement . . .

Aha! And that's why they are slightly higher. Thank you. That will give me something to ponder. . .

I should have had you for my probabality and statistics class, but that was quite a few years ago...so maybe it wouldn't have made any difference.

This has been very helpful. Thank you.