Developed by Miller/Waymire, Daytona Beach Community College
Excel Activity #5:
Using a Random Number Generator and
Constructing a Frequency Table/Histogram
Description
:
This lesson creates a uniform distribution to simulate the outcome of a state
lottery. Then Excel is used to create a frequency table and histogram.
Part I
: Creating a Uniform Distribution
To supplement state income, many states have instituted lotteries. The prizes are
dependent on the number of numbers matched, the amount of money collected
by the state in ticket sales, and the number of other players who also won. The
state makes money regardless of the number of winners. The payoffs are
computed so that the total revenue from ticket sales exceeds the total amount
paid off in cash prizes.
In one particular game, the player picks 6 numbers between 1 and 49 (in any
order) and the state computer also picks 6 numbers. If the player matches 3, 4,
5, or all 6 numbers, he or she wins. The odds of matching 6 out of 6 numbers is
approximately 1:14,000,000 and the grand prize payoff averages 7 million
dollars. This exercise will simulate the outcome of such a lottery by randomly
picking 6 numbers between 1 and 49. This will be repeated 1000 times.
1)
In cell A1, type:
=Randbetween(1,49)
and hit
ENTER
. (This function
randomly selects a number between 1 and 49 from a uniform distribution).
NOTE: If “Randbetween” does not give you a number between 1 and 49
(that is, if cell A1 contains:
#NAME?, you will have to add the function
“Randbetween” to the Excel library using the following steps
:
1A) From the “Tools” menu select “AddIns.”
(You may have to click on a
double down arrow
first to see more options on the
Tools
menu. Then select “AddIns.”)
A popup menu should come up.
1B) Check the box that says “Analysis Toolpak” and check the box that
says “Analysis Toolpak VBA.” Click on
OK
.
1C) Another menu may come up saying “
Microsoft Excel can’t run this
addin. This feature is not currently installed. Would you like to install
it now
?” Click on
YES
. The computer will take a few minutes to install
the data analysis toolpak.
1D) Once the computer has installed the data analysis toolpak, go back
to Step 1 and enter the following in cell A1:
=Randbetween(1,49)
and hit
Enter
. You should now have a number between 1 and 49.
Move on to Step 2.
2)
Click on cell A1 to highlight it. Position the cursor over the lower righthand
corner of the cell until the cursor turns into a dark plus sign.
Depress the
This preview has intentionally blurred sections. Sign up to view the full version.
View Full Document
Developed by Miller/Waymire, Daytona Beach Community College
left mouse key and drag the mouse to cell A1000. Release the mouse. The
formula from cell A1 should be filled into the remaining cells in the range.
3)
The cells A1 through A1000 should still be highlighted (if not, click on cell A1,
depress the mouse key and drag to cell A1000). With the cells highlighted,
position the cursor over the lower righthand corner of cell A1000 until the
cursor turns into a dark plus sign.
Depress the left mouse key and drag
the mouse to cell F1000. Release the mouse. The formula from cells A1
This is the end of the preview.
Sign up
to
access the rest of the document.
 Spring '08
 ihcsol
 Control key, mouse key, Daytona Beach Community

Click to edit the document details