Skip to content
Tutorials Center
My learning

Topics

Lesson 39 of 93 ยท Statistics and Math

Random Numbers and Simple Simulations

Generate random numbers in Excel with RAND, RANDBETWEEN and RANDARRAY, freeze them, draw random samples and run a simple Monte Carlo simulation.

Random numbers sound like a toy, but they do real work in a spreadsheet: picking a winner for a prize draw, splitting customers into test groups, creating sample data for a demo, or estimating the odds of something that is too complicated to calculate exactly. When you repeat a random experiment thousands of times and look at the overall pattern, you are running a simulation, and the approach with random draws is called Monte Carlo.

Excel gives you three functions to produce randomness, and one rule to remember: random values change every time the sheet recalculates. That is exactly what you want for a simulation and exactly what you do not want for a prize draw you must be able to show afterwards. This lesson covers the functions, how to freeze the results, how to sample without repeats and how to build a small Monte Carlo model. Excel cannot run on this site, so the TryIts use a seeded random generator in JavaScript and NumPy in Python as stand-ins; real Excel gives different numbers each time.

Three random functions

RAND A decimal from 0 up to just below 1.
RANDBETWEEN A whole number from low to high.
RANDARRAY A whole grid at once (spills).
1 / 2

RAND, RANDBETWEEN and RANDARRAY

  • RAND() takes no arguments and returns a decimal that is at least 0 and less than 1. Multiply and add to change the range: =RAND()*100 gives 0 up to (not including) 100, and =10 + RAND()*5 gives 10 up to 15.
  • RANDBETWEEN(bottom, top) returns a random integer from bottom to top, both included. =RANDBETWEEN(1, 6) is a die roll.
  • RANDARRAY([rows], [columns], [min], [max], [integer]) returns a block of random numbers in one formula. =RANDARRAY(5, 3, 1, 100, TRUE) spills a 5-by-3 grid of integers from 1 to 100. The defaults are 1 row, 1 column, minimum 0, maximum 1, decimals. It needs a version with dynamic arrays (see Dynamic Arrays and Spilling and, for more array tools, SEQUENCE, RANDARRAY and Array Arithmetic).

Excel's generator cannot be given a seed, so you cannot ask for "the same random numbers again". If you need repeatable randomness, generate once and keep the values, or use code such as the Python in this lesson.

Volatility: why the numbers keep changing

RAND, RANDBETWEEN and RANDARRAY are volatile: Excel recalculates them whenever anything in the workbook recalculates, and so it recalculates every formula that depends on them. Typing in an unrelated cell, pressing F9, or opening the file can all change your random values. See Performance and Spreadsheet Best Practices for why a lot of volatile cells can slow a large model.

How to keep a result:

  1. Paste as values. Select the cells, copy, then Paste Special > Values. The formulas are replaced by the numbers they showed. Do this for a prize draw, a test-group assignment or any list you must be able to defend.
  2. Manual calculation. Set Formulas > Calculation Options > Manual. Values then change only when you press F9. This is useful for a simulation you want to re-run on demand, but remember to switch it back, because a sheet stuck on manual shows stale results.
  3. F9 in the formula bar. Select part of a formula while editing and press F9 to replace it by its current value. Press Esc to cancel, or Enter to keep it as a typed value.

Run it: random functions with a seeded stand-in

To make this page repeatable, the JavaScript below uses a small seeded generator (called mulberry32) instead of Excel's. Each function mirrors the Excel behavior: the ranges, the inclusive ends and the integer rule. The numbers are not the ones Excel would give you, but the logic is the same.

JS RAND, RANDBETWEEN and RANDARRAY, seeded

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Each face should appear about 1,000 times in 6,000 rolls, but not exactly. That small, normal scatter is called sampling variation, and it shrinks (in relative terms) the more trials you run.

Random sampling and shuffling

Two jobs come up all the time.

Pick one at random. If your names are in A2:A101: =INDEX(A2:A101, RANDBETWEEN(1, ROWS(A2:A101))). Count rows with ROWS so the formula adapts when the list grows.

Pick several without repeats. RANDBETWEEN can pick the same row twice, so it is wrong for "draw 5 winners". Shuffle the list instead: give each name a random sort key, sort by it and take the first few. In modern Excel this is one formula:

TEXT
=TAKE(SORTBY(A2:A101, RANDARRAY(ROWS(A2:A101))), 5)

RANDARRAY(ROWS(...)) creates one random number per name, SORTBY orders the names by those numbers (a shuffle) and TAKE keeps the first five. See FILTER, SORT, SORTBY and UNIQUE and TAKE, DROP and CHOOSECOLS. Without dynamic arrays, put =RAND() in a helper column next to the names, rank it with RANK.EQ and pick ranks 1 to 5. Either way, paste the winners as values straight away.

To split a list into two test groups, add a helper column =IF(RAND() < 0.5, "A", "B") and paste it as values. The groups will be about, not exactly, equal in size.

Monte Carlo: estimating by repeated trials

The idea of Monte Carlo is: if you cannot work out the answer by algebra, simulate the process many times and count. Each row of the sheet is one trial; a summary formula at the bottom averages them.

A classic example is the sum of two dice. One trial is =RANDBETWEEN(1,6) + RANDBETWEEN(1,6). Copy it down 1,000 rows and a COUNTIF shows how often each total appears. Theory says the total 7 occurs in 6 of 36 combinations (about 16.7%), and the simulation should land near that.

A more business-like example is a project made of three tasks with uncertain durations, and the question "what is the chance we finish within 30 days?". You model each task with a minimum and maximum, draw a random duration per task per trial, add them up and count how many trials come in at 30 days or less. The next TryIt does exactly that.

JS Monte Carlo: two dice and a three-task project

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The "exact" column shows the true probabilities, so you can see how close the simulation gets with 20,000 trials. For the project, the exact answer is harder to find by algebra, which is precisely when simulation earns its keep. In a real sheet, make a data table or copy the formulas down thousands of rows; a What-If data table can also re-run one formula many times.

The same ideas in Python

Python (NumPy) can seed its generator, so results are repeatable, and it runs thousands of trials in one vectorized step. This runs in real Python in your browser:

Python NumPy: seeded dice, sampling and a shuffle

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice replace=False in rng.choice: it is the same "no repeats" rule as the shuffle formula. NumPy's integers(1, 7) excludes 7, unlike Excel's RANDBETWEEN(1, 6), whose top value is included. Mixing up inclusive and exclusive ends is the most common bug when you translate between them.

Good practice

  • Label simulations and keep the number of trials in a cell, so readers know how stable the answer is.
  • Do not use RAND for security. These numbers are fine for games and models, not for passwords or tokens.
  • Be clear about independence. The model above treats tasks as independent; real tasks often slip together, which makes late finishes more likely than the simulation says.
  • Keep volatile cells together on a separate sheet, and switch to manual calculation while you edit a heavy model.
  • Record the outcome of any real draw: paste values and save the file.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which formula simulates one roll of a six-sided die?

    RANDBETWEEN returns an integer with both ends included, so 1 to 6 gives exactly the faces of a die.
  2. 2. You drew prize winners with RAND, and the list changed when you edited another cell. What went wrong?

    Random functions recalculate with the workbook. Copy the results and use Paste Special > Values to freeze a draw.
  3. 3. Which approach draws 5 different names from a list in modern Excel?

    Sorting by one random key per name is a shuffle, and TAKE keeps the first five, so none can repeat.
  4. 4. Can you give Excel's RAND a seed to get the same numbers again?

    Excel's random functions cannot be seeded. To reproduce a result, store the generated numbers.
  5. 5. What is the main idea of a Monte Carlo simulation?

    Monte Carlo estimates quantities by running many random trials and summarizing the outcomes, for example as an average or a share.

Summary

  • RAND gives a decimal in [0, 1); RANDBETWEEN(a, b) gives an integer with both ends included; RANDARRAY spills a block.
  • All three are volatile; paste as values or switch to manual calculation to keep results.
  • Shuffle with SORTBY(list, RANDARRAY(...)) and TAKE to sample without repeats.
  • Monte Carlo = many random trials plus a summary such as an average, a percentile or a share.
  • Excel cannot seed its generator; NumPy can, and its ranges treat the upper end as exclusive.

Next: Excel Tables.