Activity 18.1

Simulations

10 points

Due at the beginning of class, Friday, April 24, 2009

In this activity, you will explore some ideas of probability by using Excel to simulate tossing a coin and throwing a free throw in basketball.

Fill out the sheet Activity 18.1 Worksheet (I will give you a copy of this in class!), which is the first 3 questions of this Activity, and turn it in when it is complete.

In Excel, the formula to simulate a coin-toss is =INT( 2 * RAND( ) ) . The instructions to simulate many coin tosses are:

Right click on the data portion of the graph, and choose "Select Data" from the menu. From the Legend Entry (series) menu on the left, "remove" all the columns except the last two (the one with all the 0.5's and the one with the "Proportion so far").

On the layout tab, add a Chart Title and Axis Titles. Right click on the legend and delete it. Finally, move the cursor until it is an arrow pointing to one of the horizontal gridlines. Right-click and select Delete to remove the gridlines.

Copy and paste the graph of 1000 tosses into your Word document. (Do NOT paste the column of data containing these tosses into your document.)

4. Write a paragraph explaining what your graph shows.

5. Put the cursor in any blank cell near your graph. Press Ctrl= to change the random numbers and your graph. Do this several times and describe how the graph changes. Paste this new graph into your Word document. To make sure that your previous graph does not change, you should choose "paste special," and then the option as a picture (enhanced metafile).

6. Shaquille O'Neal is not particularly good at free throws. He makes about 50 percent of his free throws over an entire season.

6. a. Go to sheet 2 and set up a new worksheet to simulate 100 free throws shot independently by a player who has probability 0.5 of making each shot. In column A, keep a record of the attempt number by generating integers 1, 2, . . . , 100 in cells A2 through A101. (Enter an appropriate title in cell A1.)

6. b. Label cell B1 Hit or Miss and generate a random sequence of 1s (each 1 represents a hit) and 0s (each 0 represents a miss) in cells B2 through B101.

6. c. Compute the overall proportion of hits by calculating the mean of the B column values. Also look at your data and identify the length of the longest streak of hits and the length of the longest streak of misses. Write a paragraph commenting on the proportion of hits and the "streaks."

Copy and paste your spreadsheet with the free throw simulation into your Word document also.  Try to use small print and use paper wisely!

7. The Juniata College men's and women's basketball teams are quite accomplished at making free throws. During the 2007-08 seasons, the men's team made approximately 77.4% of their free-throws, and the women's team made approximately 71.4% of their free-throws. (Source: http://www.juniata.edu/athletics/mbasketball/2007-08/TEAMCUME.HTM and http://www.juniata.edu/athletics/wbasketball/2007-08/TEAMCUME.HTM ).

7. a. In this problem, we will set up another new worksheet to simulate 100 free throws shot by team members who have probability 0.75 of making each shot.

7. b. Again, use column A to keep track of the attempt number in cells A2 through A101.

7. c. Label cell B1 Hit or Miss. Now you will generate a sequence of equally likely occurrences of the numbers 0, 1, 2, or 3 with 0 representing a miss and any of the other three numbers representing a hit. By entering the formula =INT( 4 * RAND() ) into cell B2, you can set the sequence up so each of the numbers occurs with equal probability. Then auto-fill to cell B101.

7. d. In cell C2, enter the formula =IF( B2=0 , 0 , 1 ). Th is will give you a "1" in cell C2 if cell B2 recorded a hit, and a "0" if cell B2 recorded a miss. Auto-fill the formula down to cell C101.

7. e. Find the overall proportion of hits, and identify the length of the longest streak of hits and the length of the longest streak of misses. Write a paragraph commenting on your proportion of hits and your "streaks."

7. f. Describe how the "streaks" compare for the 50-percent and 75-percent scenarios.

Copy and paste your spreadsheet with this free throw simulation into your Word document also.  Try to use small print and use paper wisely!

Summary
In this activity, you learned how to use Excel to simulate random processes. You simulated the random process of tossing a coin and graphed the frequency of heads to visualize the probability that the toss comes up heads. You also used Excel to simulate basketball free throws and analyzed the proportion of hits and streaks of hits and misses.