O
OssieMac
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.
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.