Chapter 5 Project – Simulations in Excel



Project – Simulations in Excel

Objective:  Use Excel for random number generation in order to answer probabilistic questions.  

Materials:  You will need Microsoft Excel with the Analysis ToolPak installed. 

Procedure:  Here¹s how to do Random Number Generation in Excel.

• Create a column of the possible digits  (i.e.  0-9, or 00-99 or 000-999, etc.)

• Put the associated probabilities of each digit in the column next to each digit. 

o For example, if every digit is equally like and you have ten digits, then each probability is 0.1.  (If you had 100 digits, each probability would be 0.01. )

o [pic] 

• Go to Tools ⋄ Data Analysis. (A pop-up window should appear.)

o Scroll down to Random Number Generation. Click OK.

o For Number Of Variables:  20

▪ This is the number of simulations you want to perform. 

▪ If you wanted to do 100 simulations, you¹d put ³100² in this box. 

o For Number Of Random:  30   (This would generate 30 random #s)

▪ Here, you can always tell it how many random numbers you want created

o For Random Seed:  Leave Blank

o For Distribution:  Discrete

▪ We¹ll work with some of the other distributions later

o For Value and Probability Input:  Click on A2 and drag to B11

▪ Obviously, if you were working with two digit  or three digit numbers, you¹d click and drag from the first possible digit (upper left) to the last possible probability (lower right)  

o For Output Range:  Click ³New Worksheet Ply² and name it ³Random Output²

▪ This will create a new worksheet that will display 20 columns of 30 random digits.     

o The window below shows how to do 20 simulations of 30 random digits and displays them in a new worksheet called ³Random Output.² 

o [pic]

SAMPLE Question: 

Corinne is a 75% free throw shooter.  In a particular game she had 12 attempts and made only 7.  Simulate 50 trials of 12 shots.  With what probability did she make 7 or fewer baskets? 

• Column A = 00-99  (Use the ³Fill Down²  feature to quickly create the list of 100 numbers)

                                               i.     Let A2 be 00.  Let A3 = SUM(A2, 1).  Then do Fill Down.

• Assign 00-74 to ³make² and 75-99 to ³miss²

• Column B = 0.01.  Use Fill Down again. 

• Use the RNG  to create 50 sets of 12 random numbers on a new worksheet. 

• For each column, you need to figure out if she made fewer than 7 baskets. 

                                               i.     Use this in A14:  =CountIF(A1..A12, ³ ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download