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.

 

[Brew]

Your explanation answers my last post, however, I will test code you send to validate the explanation given. Thank you for your assistance. I will follow-up when I've tested the code

 

[barry houdini]

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?

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

I agree with Erik

as already stated the probability of pulling out exactly 6 caramels when 30 candies are selected from 220 (with no replacement), assuming the are 73 caramels within the 220 is

=HYPGEOMDIST(6,30,73,220)= 4.39%

this is not affected by past results

the chances of pulling out a caramel with just 1 selection can be calculated by using

=HYPGEOMDIST(1,1,73,220)

but more easily....

=73/220

of course, if you don't replace the first pick then the odds will change for the next pick, e.g. if you picked a caramel on the first pick then the chances of getting a caramel on the second pick are

=72/219

while if you didn't pick a caramel on the first pick the chances of getting one on the second are

=73/219

The difference here is that the past picks are affecting the numbers left and therefore the probablility varies with each pick BUT once you go back to the start, with 220 candies in the box, the probabilities are the same as before.

 

[Brew]

Barry, cool stuff....that really drives the nail in the coffin on this one. Thanks

 

[Brew]

Erik, interesting random generator code...thanks