Today's AP Stat Lesson: EXCEL HEAVY - VLOOKUP(RANDBETWEEN(NERD, GEEK), STATGEEKS,2)

Today's(tomorrow's) plan for AP Statistics is a little Excel heavy,  something that I hope carries over for my students into college and beyond. Is there a standardized test that measures a student's increased proficiency at Microsoft Excel, or other computer apps for that matter?  Most commands involve looking up a value at random between zero and one hundred.

Each student has been keeping track of the number of sheets of paper received in each class, each day, over the span of about 2 months. The focus is on teacher created paper, so if a student uses a piece of their own notebook paper it doesn't count.  Incidentally, om pretty sure that AP Stat is dead last for every one of my students major subjects (AP Stat instructor pats himself on back).

Using this info, they are going to create a probability model for the number of sheets of paper received for a single class.

For AP Stat...

SHEETS OF PAPER    0     1      2 

PROBABILITY         0.90 0.05 0.05  

Now that the probability model is created the Excel fun can begin.  Number all cells in one column from 1-100 to represent a possible outcome. Place each possible outcome in the second column according to it's the probability you observed. Example: in the chart above, the probability of receiving 0 sheets of paper was 0.90, so spaces 1-90 would be 0. The probability of receiving 1 was 0.05, so 91-95 would be 1, and 96-100 would be 2.  Repeat for all other possible outcomes. Suggest to students that they choose a class that is at least manageable as far as different numbers of sheets of paper.  Yes, there is a way to make Excel do this, but that is a little too awesome for an AP Stat class.  

Create a new sheet for simulating new days of each class. For the first day, we are going to "lookup" random values from the previous sheet to see how many sheets of paper we will receive. The command for doing this... VLOOKUP(RANDINT(1,100),in the previous sheet, give the value in the second column in the row that the random number is in). 

Example:  =VLOOKUP(RANDBETWEEN(1,100),Sheet2!A$1:B$100,2)

Keep the dollar signs so that when you drag to autofill the formula they continue to look within the same array of cells. Autofill about 200 or 300 of these cells...or 500 :)

Next we're going to calculate the average number of sheets of paper we've received each day. AVERAGE(cell to the left and all cells above). 

Example: =AVERAGE(B2:B6)

Now we can look at the average over the long run (1000 days, or even more) and begin to build our definition of Expected Value.  Then, later, we'll do expected value the easy way in Microsoft Excel using the formula.  I wanted them to wrap their brains around the definition of Expected Value before they began using the formula: E(X) = SUM[X*p(X)].  It's pretty cool to see just how long of a run you need to make the simulation average approach the expected value.

The lesson is a bit like a cooking show, but it's the first time we are in Excel. The kids use their own data, so that's enjoyable/unique for most of them. The bigger idea of simulating outcomes is very powerful in learning statistics. I love nothing more than a student centered approach, but I would hate to say "discover how to use vlookup and randint functions in Excel. If anyone has a way to take a constructivist approach to learning Microsoft Excel, I'd love to hear it.

The Excel spreadsheet that students create