Master Random Number Generation in Google Sheets

Table of Contents

1. Introduction
2. Exploring the Rand Formula
3. Finding a Random Number Range
4. Simulating Rolls on a Dice
5. Understanding the Scenario
6. Modeling the Scenario
7. Counting the Target Number
8. Determining the Number of Tries
9. Calculating the Probability
10. Comparing Experimental and Theoretical Probabilities

Article

Introduction

In this article, we will continue our exploration into probability using Google Sheets. We will introduce two new formulas that are essential for working with random numbers. The first formula we will cover is called the Rand formula, which generates a random number between 0 and 1. We will then learn about the Randbetween formula, which allows us to find a random number within a specified range.

Exploring the Rand Formula

The Rand formula is a useful tool for generating random numbers. To use this formula, we need to enter "=rand()" into a cell. The Rand formula will then output a random number between 0 and 1. For example, if we enter "=rand()" into a cell, it might output 0.4858. This number represents a randomly generated value between 0 and 1.

Finding a Random Number Range

Sometimes, we need to find a random number within a specified range. For instance, if we want to generate a random number between 1 and 100, we can use the Randbetween formula. To do this, we enter "=randbetween(1, 100)" into a cell. The Randbetween formula takes two arguments: the low bound and the high bound. In this case, the low bound is 1, and the high bound is 100. After pressing Enter, the cell will display a random number between 1 and 100, such as 11.

Simulating Rolls on a Dice

To further understand these formulas, let's use them to simulate rolls on a dice. In this scenario, we are playing a game called Settlers of Katan, and rolling a seven means losing the game. We are curious about the likelihood of rolling a seven, so we will model the scenario using random numbers.

Understanding the Scenario

In Settlers of Katan, we win the game unless we roll a seven. To determine the probability of rolling a seven, we need to simulate the sum of two dice rolls and observe the results for various numbers of simulations. We will start with five simulations, then increase to 50 and 100 simulations.

Modeling the Scenario

To model the scenario, we need to generate random numbers for the two dice and sum them together. We can use the Randbetween formula to generate random numbers between 1 and 6 for each die. After obtaining the random numbers, we can use the Sum formula to calculate the sum of the two dice.

Counting the Target Number

To determine the number of times we roll a seven, we can use the Countifs formula. We specify the range of numbers we want to count, which includes all the sums of the two dice. We then set the criteria to be equal to our target number, which is seven. The Countifs formula will count the number of times the target number appears in the range.

Determining the Number of Tries

To calculate the number of tries, we need to count the total number of simulations. We can use the Count formula to count the number of cells containing sums of the two dice. This will give us the total number of tries.

Calculating the Probability

The probability of rolling the target number is determined by dividing the number of times we roll the target number by the total number of tries. Using the experimental probability formula, we can calculate the probability by dividing the number of successful tries by the total number of tries. For example, if we roll the target number 26 times out of 27 tries, the probability would be 26/27.

Comparing Experimental and Theoretical Probabilities

It is important to note that the experimental probability we calculate based on random simulations may not exactly match the theoretical probability. However, as we increase the number of simulations, the experimental probability approaches the theoretical probability. By comparing the experimental and theoretical probabilities, we can better understand the accuracy of our simulations.

In conclusion, the Rand and Randbetween formulas in Google Sheets allow us to generate random numbers and simulate real-life scenarios. By counting the occurrences of certain outcomes, we can calculate experimental probabilities and compare them to theoretical probabilities. This process helps us understand the likelihood of specific events occurring.