# Function to determine probability

#### Brew

##### Well-known Member
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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### erik.van.geit

##### MrExcel MVP
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
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
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
yet knowing the last 20 tries
the chance is not changing according to previous results

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

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

##### Well-known Member
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
Okay Erik, I understand your logic. Then how do we identify the changes in this problem?

#### erik.van.geit

##### MrExcel MVP
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

##### Well-known Member
I meant to say "how do we identify the chances with this problem":
you can only talk about chances

#### erik.van.geit

##### MrExcel MVP
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" "

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``````

Replies
0
Views
493
Replies
3
Views
147
Replies
7
Views
282
Replies
7
Views
378
Replies
4
Views
691

1,172,166
Messages
5,879,417
Members
433,427
Latest member
OutofOffice

### 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.

### Which adblocker are you using?

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

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