Function to determine probability

Brew

Well-known Member
Joined
Sep 29, 2003
Messages
1,569
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.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Well-known Member
Joined
Sep 29, 2003
Messages
1,569
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

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 :biggrin:
Erik
 

Brew

Well-known Member
Joined
Sep 29, 2003
Messages
1,569
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

Well-known Member
Joined
Sep 29, 2003
Messages
1,569

ADVERTISEMENT

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

Brew

Well-known Member
Joined
Sep 29, 2003
Messages
1,569
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
don't understand the meaning of your question
"identify" :confused: 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" "

Code:
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
 

Forum statistics

Threads
1,136,305
Messages
5,674,984
Members
419,540
Latest member
Nereus A

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top