Question for the Probability Mathematicians
 

Not sure if this is the best place to post this but it is for an Excel
project and I intend to incorporate the result in code so I thought I would
start here.

The marbles part is fictitious and is just representative for the purpose of
explanation.

Assume I have one million (1,000,000) marbles and on average 0.5% of them
are red with the remaining 99.5% green.

Assume that I RANDOMLY divide the one million marbles into 1000 equal
bundles each containing 1000 marbles.

How do I calculate the probable number of bundles of 1000 that will have
zero red, 1 red, 2 red, 3 red etc.

I can do this using a practical experiment in Excel 2007 and repeating the
experiment several times and then averaging the results but would like the
formulas to do it. I want to be able to use any total number and any number
of equal groups. I can incorporate the formulas into my code once I know what
formulas to use. Also I understand that sufficient samples are required to
produce a reasonable statistical model.

For interest, in my practical experiment on an Excel spreadsheet I came up
with the following rounded results:

6 bundles with zero red marbles
31 bundles with 1 red marbles
81 bundles with 2 red marbles
143 bundles with 3 red marbles
180 bundles with 4 red marbles
180 bundles with 5 red marbles
146 bundles with 6 red marbles
102 bundles with 7 red marbles
65 bundles with 8 red marbles
38 bundles with 9 red marbles
17 bundles with 10 red marbles
7 bundles with 11 red marbles
3 bundles with 12 red marbles
2 bundles with 13 red marbles
1 bundles with 14 red marbles

As always, all replies are greatly appreciated.

--
Regards,

OssieMac

Question for the Probability Mathematicians
 

Hi
p = probability of a red marble = 0.005
q = probability of a green marble = 0.995

Let X be the number of red balls out of n = 1000 then

Probability X = r is nCr(p^r)(q^(n-r)) where nCr is the bimomial
coefficient n!/((n-r)!r!) and is a button on your calculator.

Look up the Binomial Distribution if you want more details.

e.g. Probability X = 0 means n = 1000 and r = 0 so that nCr = 1 , p^r
= 1 and q^(n-r) = q^1000 = 0.006654

The expexted number of bundles out of 1000 for which X = 0 is then
0.006654 times 1000 = 6.66

e.g Probability X = 1 means n = 1000 and r = 1 so that nCr = 1000 ,
p^r = 0.005 and q^(n-r) = q^999 = 0.006687 so that probability X = 1
is 1000 times 0.005 times 0.006687 = 0.00334

The expected number of bundles out of 1000 for which X = 1 is then
0.0334 times 1000 = 33.4

regards
Paul


On Nov 21, 7:23*am, OssieMac
wrote:
Not sure if this is the best place to post this but it is for an Excel
project and I intend to incorporate the result in code so I thought I would
start here.

The marbles part is fictitious and is just representative for the purpose of
explanation.

Assume I have one million (1,000,000) marbles and on average 0.5% of them
are red with the remaining 99.5% green.

Assume that I RANDOMLY divide the one million marbles into 1000 equal
bundles each containing 1000 marbles.

How do I calculate the probable number of bundles of 1000 that will have
zero red, 1 red, 2 red, 3 red etc.

I can do this using a practical experiment in Excel 2007 and repeating the
experiment several times and then averaging the results but would like the
formulas to do it. I want to be able to use any total number and any number
of equal groups. I can incorporate the formulas into my code once I know what
formulas to use. Also I understand that sufficient samples are required to
produce a reasonable statistical model.

For interest, in my practical experiment on an Excel spreadsheet I came up
with the following rounded results:

6 bundles with zero red marbles
31 bundles with 1 red marbles
81 bundles with 2 red marbles
143 bundles with 3 red marbles
180 bundles with 4 red marbles
180 bundles with 5 red marbles
146 bundles with 6 red marbles
102 bundles with 7 red marbles
65 bundles with 8 red marbles
38 bundles with 9 red marbles
17 bundles with 10 red marbles
7 bundles with 11 red marbles
3 bundles with 12 red marbles
2 bundles with 13 red marbles
1 bundles with 14 red marbles

As always, all replies are greatly appreciated.

--
Regards,

OssieMac

Question for the Probability Mathematicians
 

Thankyou Paul. I really appreciate your help and it was well explained so
that I now understand a little (but not all) of how it works.

Now that I know that it is Binomial Distribution that I need, I found that
Excel actually has an inbuilt function, BINOMDIST and that made it very easy
for me.

Also I see that my practical experiment has the figures all in the ball park
which gives one confidence in the results produced by the function.

Anyway thanks again and your help is really appreciated.

--
Regards,

OssieMac


" wrote:

Hi
p = probability of a red marble = 0.005
q = probability of a green marble = 0.995

Let X be the number of red balls out of n = 1000 then

Probability X = r is nCr(p^r)(q^(n-r)) where nCr is the bimomial
coefficient n!/((n-r)!r!) and is a button on your calculator.

Look up the Binomial Distribution if you want more details.

e.g. Probability X = 0 means n = 1000 and r = 0 so that nCr = 1 , p^r
= 1 and q^(n-r) = q^1000 = 0.006654

The expexted number of bundles out of 1000 for which X = 0 is then
0.006654 times 1000 = 6.66

e.g Probability X = 1 means n = 1000 and r = 1 so that nCr = 1000 ,
p^r = 0.005 and q^(n-r) = q^999 = 0.006687 so that probability X = 1
is 1000 times 0.005 times 0.006687 = 0.00334

The expected number of bundles out of 1000 for which X = 1 is then
0.0334 times 1000 = 33.4

regards
Paul


On Nov 21, 7:23 am, OssieMac
wrote:
Not sure if this is the best place to post this but it is for an Excel
project and I intend to incorporate the result in code so I thought I would
start here.

The marbles part is fictitious and is just representative for the purpose of
explanation.

Assume I have one million (1,000,000) marbles and on average 0.5% of them
are red with the remaining 99.5% green.

Assume that I RANDOMLY divide the one million marbles into 1000 equal
bundles each containing 1000 marbles.

How do I calculate the probable number of bundles of 1000 that will have
zero red, 1 red, 2 red, 3 red etc.

I can do this using a practical experiment in Excel 2007 and repeating the
experiment several times and then averaging the results but would like the
formulas to do it. I want to be able to use any total number and any number
of equal groups. I can incorporate the formulas into my code once I know what
formulas to use. Also I understand that sufficient samples are required to
produce a reasonable statistical model.

For interest, in my practical experiment on an Excel spreadsheet I came up
with the following rounded results:

6 bundles with zero red marbles
31 bundles with 1 red marbles
81 bundles with 2 red marbles
143 bundles with 3 red marbles
180 bundles with 4 red marbles
180 bundles with 5 red marbles
146 bundles with 6 red marbles
102 bundles with 7 red marbles
65 bundles with 8 red marbles
38 bundles with 9 red marbles
17 bundles with 10 red marbles
7 bundles with 11 red marbles
3 bundles with 12 red marbles
2 bundles with 13 red marbles
1 bundles with 14 red marbles

As always, all replies are greatly appreciated.

--
Regards,

OssieMac