Exceljet

Quick, clean, and to the point

How to generate random values

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 exammple, 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.

Now I can simply replace the hard-coded index with 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 send the formjula down, all groups are assigned.

Note RANDBETWEEN is volatile and recalculates on each worksheet change.

Once you have random values assigned, you probably want to stop then from recalculating. The easiest way is to use Paste Special to overrite formulas with values.

Course 
Core Formula

Related shortcuts

Author 
Dave Bruns
Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables