Class 16: All Excel –
Binomial Distribution, Law of Averages, Draws from a Box
Journal assignment:
Answer
the following questions from last weekÕs HIV article:
1)
The article says that the vaccine was Ò31%
effective in preventing HIV protection in comparison with placeboÓ. What does this mean? Where did the 31% figure from?
2) What does it mean that half the
participants given placebos?
Why was this done? If the
control group had not been given placebos, how do you think this would have
affected the results of the trial?
3) If the vaccine makes no difference, how many of the 125 cases
of new HIV infection would you expect in the vaccine group and how many in the
placebo group?
4) If you flip a coin 125 times, what is the
chance that you get exactly 74 heads?
What is the chance that you get at least 74 heads? What does this have to do with the HIV
vaccine trial?
5) What do you think of this trial? Are you convinced that the vaccine
works?
In addition,
Print out two plots from the cointossing part of todayÕs computer lab – see below. Write down the number of tosses you simulated total, the number of heads you got total, how far off the number of heads was from half the number of draws, and the percent of heads you got total.
Homework Assignment for Thurs,
Oct 22:
On blackboard
Announcements
WeÕll have a Quiz next week
on Thursday, Oct. 29.
Notes on
formulas you may need to use:
LindaÕs notes for Oct 15 and Oct 20 computer labs
á =FACT(5) computes 5! which is 120
á =BINOMDIST(6, 10, 0.2, FALSE) computes the probability of getting exactly 6 ÒsuccessesÓ in 10 trials, where the probability of success on any one trial is 0.2. That is,
á =BINOMDIST(6, 10, 0.2, TRUE) computes the probability of 6 or less successes in 10 trials, where the probability of success on any one trial is 0.2.
á =RANDBETWEEN(0,1) picks either 0 or 1 at random. This is like flipping a coin. =RANDBETWEEN(1,6) picks a number between 1 and 6 inclusive at random. This is like rolling a die.
á When using RANDBETWEEN, you can hit the F9 key to generate new random numbers.
Part 1: Could the HIV Vaccine Results be by
chance?
1. Make a table similar to the following:
Number of tosses 
Number of heads 
Number of tails 
Probability of this outcome 
125 
0 
125 

125 
1 
124 

125 
2 
123 

125 
3 
122 

2. Use the formula =BINOM(k, n, p, FALSE) to fill in the last column.
3. Make the table long enough to go all the way down to 125 heads.
4. Use the SUM formula to compute the probability of getting at least 75 heads.
5. This is the probability that you would get results like the vaccine trial results or better just by chance. Are you convinced that the vaccine is effective?
Part 2: Coin tossing:
1. WeÕre going to simulate coin tossing in Excel and see what happens in the long run. Start by using =RANDBETWEEN(0,1) to simulate 1 coin toss. LetÕs think of a 1 as heads and a 0 as tails.
2. Next, simulate 10 coin tosses. How many heads did you get?
3. Make a chart like the following. Use formulas. Make it really long, like 1000 rows, by dragging handles. (Hint: for the third column, you should make a formula that adds the number of heads so far on the row just above and the outcome of the toss on the current row, something like =B2 for the first toss and =C2+B3 for the second toss, =C3+B4 for the third toss, etc.)
Toss # 
Outcome of toss (1 = head, 0 = tail) 
# Heads so far 
Half of the number of tosses so far 
Difference between the number of heads so far and half the number of tosses 
Percent of heads so far 
Difference between percent of heads and 50% 

1 







2 







3 







4 







4. What happens in the long run?
5. Try plotting toss number on the x axis and difference between number of heads and half the number of tosses on the y axis. The easiest way to do this is to select the toss number, hold down the control key, and then select other column you want to plot. Next use the Insert tab, to insert a plot. Pick a scatter plot. What does it look like? Please label axes and save your work so that you can include a printout of this plot and the next one for your journal.
6. Try plotting toss number on the x axis and difference between the percent of heads and 50% on the yaxis. What does it look like?
Part 3, if there is time.
1.
Pretend you have
a box with 6 tickets in it, numbered 1, 2, 3, 4, 5, 6. Suppose you draw 25 tickets with
replacement and add up their numbers.
Roughly what would you expect to get for the sum?
2.
Now simulate it
in Excel using =RANDBETWEEN(1,6) written 25 times down a column (or across a
row). What is the sum? (Use the
SUM formula.)
3.
Use F9 to redraw
10 to 20 times. Write down the
sums you get.
4.
What is the mean
sum? About how far off are the
sums you get from this mean?