Analysis ToolPak - Random Number Generation 1

[AzizKamal]

I am using Analysis ToolPak in Excel 2010. I added Analysis ToolPak by checking it in Add-Ins dialog box. My goal is to generate random numbers from 1 to 5 for 50 rows of one column. From cells B1 to C5, I made the table as follows:

Column B  Column C
1  0.2
2  0.1
3  0.4
4  0.1
5  0.2

Then I selected cell A1 and clicked:
Data -> Data Analysis -> Random Number Generation -> OK

I entered the following values in Random Number Generation dialog box:
Number of Variables: 1
Number of Random Numbers: 50
Distribution: Discrete
Value and Probability Input Range: $B$1:$C$5
Random Seed: I left it as blank
Output Range: $A$1

With these selections, I clicked OK.

I got random numbers between 1 to 5 in cells A1 to A50. However, the distribution of random numbers is not according to the probabilities defined. For example, probability of 1 is defined as 20%, so 1s should be 10. Similarly, 2s should be 5, 3s should be 20, 4s should be 5, and 5s should be 10. However, actual data in A1 to A50 showed the following results:

Total 1s: 12
Total 2s: 5
Total 3s: 18
Total 4s: 3
Total 5s: 12

 

[strongm]

I don't think that the random number generation is doing what you think it is doing. Consider a coin, with a 50/50 chance of coming up heads or tails. It would be perfectly statistically reasonable in a run of 10 tosses to get 10 heads. Technically, you only really get the expected 50/50 distribution in an infinite number of tosses, but in the real world can get close if you have a large number of tosses (e.g 10000).

The same is true for your random number generation; you'll only get close to your expected distribution if you generate a lot of numbers. 50 won't really do - and the result you are seeing is not unexpected.

 

[AzizKamal]

Thanks strongm. The explanation is very helpful in understanding the process at a conceptual level.