Let X be the number of matches between a player’s 20 selected numbers and the 20
numbers drawn by the computer. Then X may range from 0 (no match) to 20 (all match)
and follows a hyper-geometric probability distribution.
Complete all of the following steps (a – j) in worksheet Part 1 of the Excel workbook
provided. All cells should contain formulas.
a. Construct a tabular probability distribution for X in column E of the worksheet.
b. Construct a tabular cumulative probability distribution for X in column F of the
c. Create a graphical probability distribution for X.
d. Create a graphical cumulative probability distribution for X.
e. Calculate the theoretical expected value (mean), the theoretical variance, and the
theoretical standard deviation of X in the spaces provided for those quantities. Interpret
those values in your Word report
f. In column M of the worksheet, use the Excel function “=RAND()” to generate 1000
random values according to the standard uniform probability distribution.
g. In column N of the worksheet, use the Excel “=VLOOKUP()” function along with the
available tabular cumulative distribution of part (b) to randomly generate 1000 values
of X according to the described Hyper-geometric probability distribution.
h. Calculate the experimental (simulated) expected value (mean), the experimental
variance, and the experimental standard deviation of X in the spaces provided for those
i. Complete the table in columns Q, R, and S of the worksheet. In completing this table,
you should calculate the experimental means successively after n = 20, 40, 60, 80, 100,
200, 300, 400, 500, 600, 700, 800, 900, and 1000 simulations. It is natural that the
calculated experimental means are refreshed after each new operation in the
worksheet. For the Theoretical mean of X in column S, use the fixed value of the
theoretical mean calculated in part (e).
j. Create a line plot of the Experimental mean values versus the number of simulations
(n). Add the horizontal line plot displaying the theoretical mean of X. Use the f9 function
of your keyboard (Mac: fn+f9) to run several simulations of the successive experimental
means. Interpret your observation in the context of the Law of Large Numbers (as the
number of simulations become larger, the experimental values of the means approach
to their theoretical value).
Complete all of the following steps (a – i) in worksheet Part 2 of the Excel workbook
a. A normal population is given in column E of the worksheet. Calculate the mean, the
variance, and the standard deviation of this population in the designated cells.
b. Construct a Relative Frequency Histogram of the given population. Discuss the shape of
c. Using the random sampling method described in the Instructor Perspective (or
otherwise; e.g., using the Data Analysis ToolPak), randomly draw 30 samples with each
sample consisting of 30 measurements from this population. These samples will occupy
columns N through AQ.
d. For each sample, calculate the sample mean, the sample variance, and the sample
standard deviation in the designated cells.
e. Calculate the average of 30 sample means, the average of 30 sample variances, and the
average of 30 sample standard deviations in the designated cells K2, K3, and K4
f. Compare your results of part (e) above with those obtained for the population in part
(a). Discuss similarities and contrasts in the context of the Central Limit Theorem.
g. Construct a relative frequency histogram for the 30 sample means obtained from part
(e) above. Comment on the shape of the distribution of the sample means.
h. Summarize your findings of this experiment in your Word report
Format & Guidelines
The report should follow the following format: