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.
Syntax
= RANDBETWEEN([bottom], [top])bottom = minimum (floor) number able to be generated
top = maximum (ceiling) number able to be generated
Explanation
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.
Examples
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.
= RANDBETWEEN(X,Y)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:
= RANDBETWEEN(0,1)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.
= RANDBETWEEN(1,6)
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)