Random binomial numbers

[Ken Schmidt]

Is there a way to generate random binomial distribution numbers in Excel? I
know about the Analysis Toolpak generator, but it is faulty. Here is an
example of what I want. Let's a baseball player is a .300 hitter. For
N=10, I would like the generator to return (on average), zero 2.8% of the
time, one 12.1% of the time, two 23.3%, three 26.7% etc, which are the
binomial probabilities. Thanks for any help. Using Excel 97.

Ken

 

[Tushar Mehta]

Suppose C1 contains the probability of success (0.3 in your example).
Suppose F1:F11 contain the values 0..10.

Then, in D1 enter =COMBIN(10,F1)*$C$1^(F1)*(1-$C$1)^(10-F1). Copy D1
to D211.

In E1 enter 0 and in E2 enter =SUM($D$11). Copy E2 down to E3:E12.

Now, the formula =VLOOKUP(RAND(),E1:F11,2,TRUE) will give you the
random number you want.

Generate multiple random numbers by selecting a range, say G2:G10001
and array-entering the formula. It generates 10,000 numbers in the
blink of an eye.

The next part, checking the results, is slow when done for the first
time. Subsequent recalculation (with the F9 button) is fast. In
J1:J11 array-enter =FREQUENCY(G2:G10001,F1:F11). In J12, enter =SUM
(J1:J11). In K1, enter =J1/$J$12. Copy K1 down to K2:K11. Compare K
with D.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Ken Schmidt]

I tried your suggestion and it works! Pretty clever stuff. Your use of the
RAND function inside the VLOOKUP formula got me wondering if there might not
be a single cell solution to this. I was able to find such a solution. The
formula is (using my example): =CRITBINOM(10,0.3 RAND()). It seemed too
simple to work, but I ran 10,000 trials using it, and tested the results via
COUNTIF, and the fit was extremely good. Thanks again for pointing me in
the right direction.

Ken