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. Once you have random values assigned, you’ll probably want to stop this recalculation. The easiest way to do that is to use Paste Special to overwrite the formulas with values.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
