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