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?

Print out two plots from the coin-tossing 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 y-axis.  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?