How to Use a Spreadsheet to Model a Probability Experiment
Stage 1We start with perhaps the most important feature of the model – the ability to produce convincingly random data. Our spreadsheet contains a single formula in cell A1 at this stage:
In this very simple model, each trial needs to produce either Heads of Tails, each with probability 0.5. The formula that we have entered will randomly choose either 0 or 1 and place the result in A1. This is not quite the ‘Heads or Tails’ that we are looking for, but it is a good start. Check that you can force the spreadsheet to recalculate, and display another random value; Function-F9 will (normally) recalculate an Excel spreadsheet, but if this does not work then entering or changing the value in any unused cell will force recalculation.
Want to keep
Cambridge University Press online course,
The general form of the IF() function is:
= IF(A1=1, “H”, “T”)
In this case, the formula means:
=IF(logical test, result if test is true, result if test is false)
- Look at the value in cell A1. Is it equal to 1?
- If it is equal to 1, the result is “H”.
- If the value in A1 is not equal to 1, the result is “T”
Stage 3So far, we have been simulating an experiment with one trial – flipping a single coin. In the question that inspired this simulation, 200 coin flips took place; this stage of the simulation takes this into account. Scroll down the spreadsheet – you will see that the random digits and H/T results go all the way down to row 200. Of course, we did not enter each of the formulae an additional 199 times! Instead we simply selected cells A1 and B1, and filled them down to row 200.
We now have the 200 random results that we wanted; the next stage is to find a way to analyse the data.One way to achieve this in Excel is to drag from cell A1 all the way to cell B200, so that the range A1:B200 is selected; then choose Fill > Down from the Edit menu.
A quicker option is to select cells A1 and B1, and simply drag the square ‘Fill handle’ that appears in the bottom-right of B1 down to B200.
Stage 4Our analysis starts by counting the number of Hs in the range B1:B200. To keep things simple, we enter the value that we are looking for (‘H’) in cell C1. Now we enter a formula in cell C2:
= COUNTIF(B1:B200, C1)
- This formula can be thought of as meaning ‘Count how many cells there are in the range B1 to B200, where the value stored in the cell is the same as that stored in cell C1’
Stage 5We now need to find the number of Tails. There are a number of ways we could do this, but we have chosen a method that illustrates a powerful feature of spreadsheets – the use of absolute and relative referencing. To see what this means, click on any of the cells in column B. For example cell B9 contains the formula:
Notice that we never typed this formula anywhere; the formula we entered in cell B1, back in Stage 2, was:
= IF(A9=1, “H”, “T”)
Our spreadsheet was smart enough to realise that when we copied the formula down the column, what we really meant was something like ‘if the cell one place to the left contains a 1, put a H in this cell; otherwise, put a 0’. This is relative addressing, and it is very often what we want to happen when we copy a formula from one place to another. In this stage (stage 5), we are trying to find the number of Tails in column B. We could enter ’T’ in cell D1, and copy the formula in cell C2 over to D2; but this would not work. The problem is that the range that would be searched for Ts would automatically be changed, thanks to the spreadsheet’s default relative addressing, to C1:C200 when it is copied across one column. Fortunately, we can prevent this by telling the spreadsheet that we always want to inspect values in column B, no matter how far across we copy the formula. To do this, we change the formula in C2 to:
= IF(A1=1, “H”, “T”)
The Dollar signs in front of the Bs effectively ‘pin’ the range to column B. We can now copy the formula across to D2. Notice that the copied formula becomes:
= COUNTIF($B1:$B200, C1)
The pinned Bs have not changed, but C1 has been updated to D1. The formula will look in the same place, but it will be looking for Ts instead of Hs.
= COUNTIF($B1:$B200, D1)
Stage 6We would like to be able to change the number of trials. We enter the number of trials that we would like to use in cell F2. Next we need to find a way of telling the spreadsheet which range to look for Hs and Ts in, based on the number of trials in the experiment. Cell G2 contains this formula:
Try changing the value in F2. You will see that the contents of cell G2 now indicate the range that the spreadsheet should be looking for Hs and Ts in. Look at the formula in cell C2. This is now:
= “B1:B” & F2
The INDIRECT() function takes a piece of text and interprets it (if possible) as a reference to a range of cells.In this example, the formula uses the range specified in cell G2 as the range to search for Hs and Ts in. Try changing the number of trials by varying the value in cell F2.
Stage 7To complete our spreadsheet model, we make a number of changes:
- We copy the formulas in columns A and B down to row 1000 so that we can explore larger numbers of trials.
- We calculate the relative frequencies of Heads and Tails and display them as a pie chart.
- We do some tidying up and formatting.
Our purpose is to transform access to education.
We offer a diverse selection of courses from leading universities and cultural institutions from around the world. These are delivered one step at a time, and are accessible on mobile, tablet and desktop, so you can fit learning around your life.
We believe learning should be an enjoyable, social experience, so our courses offer the opportunity to discuss what you’re learning with others as you go, helping you make fresh discoveries and form new ideas.
You can unlock new opportunities with unlimited access to hundreds of online short courses for a year by subscribing to our Unlimited package. Build your knowledge with top universities and organisations.