In order to bring in a level of curiosity, you could start with a very open question like “Is anything truly random?”
- Explain to students that they will, in real time, create a spreadsheet that simulates the random tossing of a coin. (While doing, this, draw attention to the spreadsheet features students will be using – see the information under 'Teacher background'.)
- Discuss random numbers and the challenge that a programmer faces in generating them. At this point, as an aside, discuss the vexed issue of randomness: are random functions as used by computers truly random? Is a truly random function possible? Is a physical coin toss truly random? How reliable are the built-in random functions such as those below?
- Next, students respond to your questions and observe a demonstration, producing an identical spreadsheet as the lesson progresses. The class provides suggestions at appropriate steps.
The process for creating the spreadsheet is detailed in the 'Method' section below. You can 'cue' students to develop solutions at points marked *.
*Ask students to locate a random function in their spreadsheet application:
This function generates a decimal number anywhere between 0 and 1
Students will also use:
- Type title Coin toss in Cell A1.
- Add successive column headings, commencing in A2: Toss, Random, Coin, Counts.
- Type title HEADS in D3.
- Type title TAILS in D4.
- Type title TOTAL in D5.
- Explain that these indicate the cells where the totals of the outcomes will be stored for each toss.
- Show how to format these cells with background colours.
- Enter '1' in A3.
- *We need to type the integers 1 to 100 down the column. Ask students to suggest a quick method of achieving this (ie enter formula =A3+1 in Cell A4).
- Highlight this formula in A4 and Edit>Fill down to Row 102 (thus creating 100 coin tosses).
- *Ask for suggestions of how we should use the Random formula. Ask: 'What range of output does it produce?' Work with the class to deduce how this could be used to randomly produce one of two random outputs: an H or a T.
- Enter formula =RAND() in Cell B3.
- Highlight B3 and Edit>Fill down to Row 102.
- * Using class discussion establish the use of the IF formula as a binary selection based on a test case. Make sure the class understands this formula's structure and logic.
- After the class establishes the following, enter this formula in C3: =IF(B2>=0.5, "Heads","Tails") This creates the outcome of Heads or Tails based on the random number generated in B3.
- Highlight C3 and Edit>Fill down to Row 102.
- * Ask the class to suggest how we could total all the Heads. Ask them to discover a function that will do this. Explain how the list of functions is divided into logical categories. When resolved, students go to Cell E3 and enter the formula =COUNTIF(C3:C102,"Heads")
- In Cell E4, enter the formula =COUNTIF(C3:C102,"Tails")
- In Cell E5, enter formula =SUM(E3:E4)
- Finished. Press F9 to recalculate (ie toss 100 coins and total the outcomes!)
Ask students if we could create a button to automate the toss action. This is demonstrated in Coin toss spreadsheet with macro (XLSM). First, a macro for F9 is created, then a button is drawn (available under Developer on the Excel ribbon controls), then it is assigned to a Form Button control by right-clicking > Assign Macro...
An alternative formula is:
=RANDBETWEEN(bottom integer, top integer)
This generates an integer (no decimals!) that is between or includes the two given integers.
=IF(Cell=1, "Heads", "Tails")
Students can also devise other problems that could be automated using a spreadsheet.