What if life’s major choices are to be decided by the flip of a coin? Interestingly, the Freakonomics Experiments project – a collaboration between Freakonomics and the University of Chicago – is enlisting volunteers to do just that to study human behavior of decision-making. Intriguing, right?
An essential feature of this experiment is generating a truly random toin coss. While the Freakonomics Experiments team ensured coin toss randomness by using the Swiss-based Fourmilab’s True Random Number Generator (more info here), we can create our own coin toss using spreadsheets! Really? You bet!
Problem-Solving Overview
Let’s break down this problem into two steps. First, we need a way for the spreadsheet to generate a random outcome. Importantly, we need to ensure that two types of outcomes occur with equal probability (that is, we want the probability of flipping a “Heads” to be 0.5 and the probability of flipping a “Tails” to also be 0.5). Next, we will want the spreadsheet to return the words: “Heads” and “Tails” rather than deliver a number. This last step can be accomplished using the quintessential logical function – the IF function.
Let’s dig a bit deeper now!
How to create a random coin toss
Step 1: Our goal in this step is to find a way to generate a random outcome. The good news is that there are spreadsheet functions that can do just that: the function names are RANDBET and RAND. Below is a video that introduces both functions. We can create a coin toss using either the RANDBET function or RAND function – we’ll show both!
Here is a summary of the two randomize functions:
- RANDBET(bottom integer, top integer) generates an integer (no decimals!) that is between the two integers (inclusive)
- RAND() requires no arguments. The output of RAND() will always be a number between 0 and 1 (e.g. 0.235456, 0.592356, etc).
If using the RANDBET function, we will set up the formula as RANDBET(0,1) so that the output that is randomly generated is either 0 or 1.
Step 2 is to create a formula that will return the words “Heads” or “Tails” based on the randomized output generated from the formula created in Step 1. To do this, we’ll use the IF function (see the Resources section below for links to video tutorials). Why the IF function? Using the IF function, we can specify that if the RANDBET(0,1) function returns a 0, then return a “Heads”. Otherwise, if the output is anything other than 0 (which in this case is always going to be 1), then the output will be “Tails”. Of course, you could have specified “Tails” if the output is 0 and “Heads”, otherwise. The order does not matter because each has an equal probability of occurring.
If using RAND(), you’ll notice the IF function set-up has a slight twist. We know that RAND() will generate a number anywhere between 0 and 1. We also want to be sure the outcome of Heads or Tails occurs with equal probability. Therefore, we’ll cut the universe in half (0 to 0.5) and (any number greater than 0.5 to 1). Now, to set up the IF function, we start as follows: If the RAND() output is 0.5 or less, then return “Heads.” Otherwise (meaning that the output is any integer greater than 0.5), the output that should be returned is “Tails.”
To toss the coin again means you’ve got to refresh the calculations. An easy way to do that is to CTRL + R on your keyboard. Check out the spreadsheet below. Here in the post you cannot refresh the calculations because it is embedded in the post, but feel free to save the spreadsheet (link is in the Resources section below) to your Google drive and refresh the calculations there.
[googleapps domain=”docs” dir=”spreadsheet/pub” query=”key=0ArU-OSCYb_YpdE9SSlBreVV6UkZZVFFSd2lIaWVWTWc&output=html&widget=true” width=”640″ height=”300″ /]
Now that you’ve mastered randomize functions, try to build a dice! If you can do it without watching the video tutorial, you know you’ve mastered these concepts.
Resources:
Hi, just wanted to say, I loved this post. It was practical.
Keep on posting!