top of page

How to use the RANDBETWEEN Function in Excel - 4 Examples

The RANDBETWEEN function is an extension of the RAND function, and generates a random number between two input values. By defining a minimum (floor), and a maximum (ceiling), every time the workbook is refreshed a new number within that set boundary is generated.


= RANDBETWEEN([bottom], [top])

bottom = minimum (floor) number able to be generated

top = maximum (ceiling) number able to be generated


This function is part of the "Math and Trigonometry" function group. Its primary purpose is generate a random numbers within a set range of values. It is useful in situations where randomness or chance needs to be introduced to test specific workbooks and models (rolling dice n times, coin-flips, creating reasonable fake numbers).

RANDBETWEEN is a volatile function, which means that the value generated will change every time any cell in the workbook is changed or updated.

The RANDBETWEEN function will only return integers/whole numbers. No decimal numbers will be created, even if the limits are set to 0 and 1.

If the minimum is not smaller than the maximum, an #NUM! error will be thrown. An error will also be thrown if you are missing a top or bottom argument.


1. How to Generate a Random Number Between Two Values

By using the RANDBETWEEN function will return a whole number that falls within the top and a bottom parameters.


Will generate a random number between 5 and 25. Cell references can also be used:

2. How to Simulate a Coin Flip

In this example, in each of the "Coin Flip Simulator" cells below, we are using this formula:


Since the function will only return a 0 or a 1, there is a 50/50 chance of either occurring. By calling heads 1, and tails 0, we can simulate as many coin flips as we need. In the example below we are using 24. In this specific instance, 14 were heads and 10 were tails. As we increase the number of simulations we would expect this number to reach a more consistent 50% heads/tails spread.

3. How to Simulate a Dice Roll

Like with the example above, we are using the RANDBETWEEN to represent a dice being rolled. The bottom is 1, and the top is 6, representing the 6 possible sides that it could land on. Each simulator cell contains the below formula, and the cells on the right are adding up the occurrences of each of those specific numbers. The chart shows us the distribution of rolls. As we keep adding simulations the distributions should trend towards an even 1/6th split.


4. Use RANDBETWEEN to Create Realistic Fake Numbers

If you're in need of fake numbers to test a model, RANDBETWEEN can be helpful. It is a pain to manually type in a large amount of random 5 digit numbers. Instead, let RANDBETWEEN generate plausible numbers, then just value them out.

In this example, lets say we need to create some fake monthly sales numbers. If we know that throughout the year we usually generate sales of around $50,000 to $75,000, then by using the formula below, we can quickly generate some fake numbers to test our workbook without having to type in fake numbers by hand.

= RANDBETWEEN(50000,75000)

bottom of page