How to generate random values – Best article

In this video, we’ll look at a few ways to generate random values with the RANDBETWEEN function.

The RANDBETWEEN function is a simple function you can use to generate random numbers. For example, I can enter RANDBETWEEN with a bottom value of 1 and a top value of 100:

When I press enter, I get a random value between those values.

So, I can just drag the fill handle to get get 10 random numbers between 1 and 100.

You can extend RANDBETWEEN to do all kinds of clever things. To get random prices between 10 and 50 dollars, I can again use RANDBETWEEN, and then format the result as Currency.

To get more natural prices, I can just change the formula to subtract 5 cents:

I’m using the shortcut control + enter here to enter all formulas at once.

You can also use RANDBETWEEN to get generate random dates.

For example, I can use the DATE function inside RANDBETWEEN to generate random dates between January 1st and June 30:

I could also put these dates on the worksheet and then point to those cells inside RANDBETWEEN. I need to make these making the reference absolute with F4 to prevent changes. This makes it easier to change the dates being used by RANDBETWEEN.

I can even combine this approach with the the WORKDAY function to generate random working days.

Finally, you might wonder how you can generate random text values?

A simple way to do this is to combine RANDBETWEEN with the CHOOSE function.

Let’s say I want to assign this list of a thousand people to 4 random groups: fox, bear, otter, and moose.

I can start off using CHOOSE with a hardcoded index of 1, followed by the group options.

When I press enter I get fox, since fox is the first item in the list. And if I change index to 2, I get bear.