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 two values.
So, I can just drag the fill handle down to 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 just format the result as Currency.
To get more natural prices I can modify 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 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 references 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 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 hard coded 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.
Now I can simply replace the hard coded index with a number generated by RANDBETWEEN. Since we have 4 groups, the lower value is 1 and the upper value is 4.
When I double-click the fill handle to copy the formula down, all groups are assigned.
Remember that RANDBETWEEN is a volatile function. It will recalculate whenever you change the worksheet.