Function to determine probability

[Brew]

After performing several hundreds trials, of pulling out candy from a pool of 14 different varieties, one by one, in sets of 30 pieces.
I have recorded the number of caramels pulled from each trial set.
I found that 18.25% of the time, I would pull out 6 caramels from
a set of 30 pieces of candy pulled.
Now Comparing the results from the last 20 trials of how many caramels were pulled out of each set of 30 pieces of candy,
What function do I use to return the probability
that 6 caramels will be pulled from the next set of 30 pulled?
Sample data:
last 20 trials (# of caramels pulled from each set of 30):
A1:A20=11,2,7,7,12,8,7,7,7,5,12,8,5,9,6,4,10,8,7,7
In the current example 6 caramels pulled has only occurred
once in the last 20 trials.
Im not sure if the HYPGEOMDIST function is the closet solution type.

 

[erik.van.geit]

can you clarify

how you did your test ?

not this way I presume
although this is what I understand from your post
box with 14 different candies
pull out 1 candy and throw it in the box again
do this 30 times
check how many times your caramel was pulled
the 18.25% seems way to high

anyway: would your question be similar to the following one ?

when throwing a die you have 6 possible numbers
you want to determine how many chance you have to throw a six
a. for your first try or any try with no information beforehand
b. knowing the last 5 throws had no 6
(we are considering the die is perfect and will be thrown by a machine in a completely random manner)

kind regards,
Erik

 

[barry houdini]

If you know how many candies in total there are and how many of those are caramel then you can calculate the probabilty of pulling out 6 from 30 pulled.

The historical data is irrelevant to that calculation

As you say, you can use HPYOGEOMIST

If you have a box or 85 candies, 20 of which are caramel then the probability that you pull out 6 caramels from 30 selections (without replacement) is

=HYPGEOMDIST(6,30,20,85) = 18.41%

The chances of you pulling out 6 caramels remain at 18.41% no matter how many times you have pulled out 6 in previous attempts

 

[Brew]

Erik,
Your assumptions as to my explanation of the problem are basically correct. Although, to add clarification to the problem. There are actually,
220 different candies and 73 of them are caramel. After several hundred
30 set attempts of pulling one by one. I only recorded how many
times 0 through 14 caramels were pulled out of each set of 30. Data shows that 6 caramels were pulled out the most at 18.25%,
a) Now with results over the last 20 trials sets of 30,
A1:A20=11,2,7,7,12,8,7,7,7,5,12,8,5,9,6,4,10,8,7,7
what is the probability I will pull out 6 caramels in the next trial set of 30?

Also, I like your scaled down example with using dice. It applies directly to a second result I would need to know:
b) What is the chances that one of the 73 caramels from the pull of 220 candies will be pulled from the next (first) try or any try with no information beforehand, yet knowing the last 20 tries?
see sample below:
C1:C20=N,N,N,N,Y,N,N,Y,N,N,N,N,N,N,Y,N,N,N,Y,N
(Y represents a successful try, and N represents an unsuccessful try)

 

[erik.van.geit]

yet knowing the last 20 tries

your questions are not relevant
the chance is not changing according to previous results

please understand the following:
as long as we are talking about mathematical chance like throwing a die or a coin or lottery:
what has happened before doesn't influence in any way what will happen afterwards

therefore I had already the answer in my previous reply: check the bolded characters: together they make
it is IMPOSSIBLE to predict results, you can only talk about chances and they don't change even when you know 1.000.000.000 previous results

lottery (ab)uses some properties of human mankind
one of these is, they THINK they can predict the results

but you CAN'T: it is as simple as that
Erik

 

[Brew]

Thanks Barry for the clarification on the usage of the Hypgeomdist function,
I can use it as the following:
=HYPGEOMDIST(6,30,73,220)
However, I think my previous post reply to Erik adds more clarity to my problem and explains a need slightly outside the scope of this function

 

[Brew]

Okay Erik, I understand your logic. Then how do we identify the changes in this problem?

 

[erik.van.geit]

Okay Erik, I understand your logic. Then how do we identify the changes in this problem?

I do not understand this question.
Do you, Barry ?

 

[Brew]

I meant to say "how do we identify the chances with this problem":
see your quote below

you can only talk about chances

 

[erik.van.geit]

don't understand the meaning of your question
"identify" chances ?

if you throw a die there is one chance out of 6 to get a "1"
so the chance is 1/6
the only "prediction" can be: "if you throw a die 1.000.000 times, you will get around 1.000.000/6 times a "1" "

Option Explicit

Sub throw_die()
Dim i As Long
Dim cnt As Long

Randomize Timer

Const highNum = 5000000
Const pickData = 10

    For i = 1 To highNum
    cnt = cnt - (1 = Int(Rnd * pickData) + 1)
    Next i

MsgBox cnt & "/" & highNum & vbLf & "chance = " & Int(highNum / pickData) & "/" & highNum, 64, 1 & "/" & pickData
End Sub