Most users are familiar with RAND(). This function doesn't take an argument; it just returns a random value between 0 and 1. To generate a random number between 0 and 10, you'd multiply the results of RAND() by 10; to generate a random number between 0 and 100, you'd multiply by 100, and so on. If you want random integers, you'd wrap the RAND() function in an INT() function.
As just shown, generating random numbers between 0 and another number is relatively easy. Generating other types of random values, such as dates, isn't. It can be done, but it requires a convoluted formula that's hard to remember. Fortunately, that's unnecessary, thanks to RANDBETWEEN().
Excel's RANDBETWEEN() function returns a random integer number between two numbers, the function's arguments, in the form
where bottom is the lower value and top is the upper value. For instance, the function RANDBETWEEN(27,100) would return random values between 27 and 100. If you transpose the two arguments, the function will return an error.
In Excel 2003, you must install the Analysis ToolPak add-in to access this function, as follows:
- Choose Add-Ins from the Tools menu.
- In the resulting dialog box, click the Analysis ToolPak option.
- Click OK.
With just a little more work, you can use RANDBETWEEN() to generate random dates for a specific time period. You'll include the DATE() function to specify the bottom and top values as dates, using the form
For example, to return random dates between January 1, 2012 and January 30, 2012, you'd use the following function:
Being random, resulting dates might repeat, but they'll always fall within the specified dates.
When using RAND() or RANDBETWEEN() you'll probably want to turn off Excel's automatic recalculation or use Paste Special's Values option to replace the function with its evaluated result. That's because these functions recalculate every time you change the sheet.
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.