Spreadsheet Simulations Without Coding

Why use a spreadsheet

You do not need to write a program to simulate chance. Any spreadsheet application can generate random numbers, apply a rule to each one, and summarise the results. The method is slower to scale than code, but it is visual: you can see every trial in its own row, which is useful for learning and for checking a hand calculation.

The functions named here exist in the common spreadsheet programs, though details can vary slightly between applications. Check your program’s help if a formula behaves differently.

The two key functions

• RAND() returns a random decimal between 0 and 1. Any value in that range is treated as equally likely. It takes no arguments.
• RANDBETWEEN(low, high) returns a random whole number from low to high, with both ends included. So RANDBETWEEN(1, 6) behaves like a fair die.

Both are “volatile”: they produce a fresh value whenever the sheet recalculates, which in many programs happens whenever you change any cell. That is useful for repeating the experiment and a nuisance when you want a result to stay put.

Simulating a coin toss

Put this in cell A2:

=IF(RAND() < 0.5, 1, 0)

It gives 1 (heads) when the random number is below 0.5 and 0 (tails) otherwise. Because RAND is spread evenly over 0 to 1, half the range falls below 0.5, so heads has probability one half. Copy the formula down 1,000 rows.

Then, in a separate cell, use =AVERAGE(A2:A1001) to get the share of heads. It should be near 0.5, but not exactly. With 1,000 tosses the typical wobble is about 0.016, since the square root of (0.25 / 1,000) is about 0.0158, so a result between 0.47 and 0.53 is unremarkable.

To model a biased coin, change the threshold: =IF(RAND() < 0.3, 1, 0) gives an outcome with a 30% chance of 1.

Simulating two dice

Use two columns for the dice and a third for the total:

• A2: =RANDBETWEEN(1, 6)
• B2: =RANDBETWEEN(1, 6)
• C2: =A2 + B2

Copy the three cells down 1,000 rows or more. To count how often a total of 7 appears, use =COUNTIF(C2:C1001, 7). The exact probability is 6/36, about 16.7%, so with 1,000 rows you expect around 167 sevens, with natural variation of roughly 12 either way. The standard deviation is the square root of (1,000 x 1/6 x 5/6), about 11.8.

You can build a small table listing the totals 2 to 12 with a COUNTIF for each, then divide by the number of rows to get frequencies. Compare them with the exact two-dice values and you will see them fluctuate around them.

Asking a “how often” question

Many questions reduce to one row per trial and a column that says whether the trial was a success.

Example: what is the chance of at least one six in four dice rolls? Use four columns of RANDBETWEEN(1, 6), then a fifth column:

=IF(COUNTIF(A2:D2, 6) > 0, 1, 0)

Copy down, then take the average of the fifth column. The exact answer is 1 - (5/6)^4, about 0.518, so your estimate should be near that. This is a good example of checking a hand calculation against a model.

Freezing and rerunning

Because the functions recalculate, the numbers change constantly. Two ways to handle this:

1. To rerun on purpose, use the recalculation shortcut of your program (often F9 in desktop spreadsheets), which gives a fresh set of trials. Write down or copy each estimate to see how much it varies.
2. To keep a result, copy the cells and paste them as values only. The formulas are replaced by fixed numbers that no longer change.

Repeating a run twenty times and recording each estimate is an easy way to see sampling noise directly.

Limits of the approach

• Size. A sheet with hundreds of thousands of rows becomes slow. Code is better for millions of trials.
• Number of trials. The estimate’s error shrinks only with the square root of the trials, so more rows help slowly.
• No fixed seed. Spreadsheet random functions generally cannot be seeded, so you cannot reproduce a run exactly unless you paste values.
• Fairness of the generator. For learning, the built-in functions are fine. This is not the right tool for anything where randomness must be unpredictable.
• Model errors. A formula that does not match the real rules gives a precise but wrong answer. Cards dealt without replacement, for instance, need more care than dice.

Where it fits

A spreadsheet is a good bridge between pencil-and-paper calculation and code. It is particularly helpful for seeing how outcomes scatter, because you can plot a column as a histogram with the program’s chart tools. The Monte Carlo simulation idea is the same as in a program: repeat a random experiment and count.

Remember that simulations describe the behaviour of the model you built. They do not reveal a way to beat games with a house edge, where each outcome is independent of the last. Gambling can be harmful, and anyone struggling with it can contact a local support service.