Periodically you may have a need to assign random numbers to an employee list, such as for raffle drawings or drug testing. In this article I’ll show how you can use the RANDBETWEEN function together with an Excel pivot table to pull a randomized assortment of employees.
The RANDBETWEEN function was introduced in Excel 2007 and has two arguments:
- Bottom – The smallest random number that could be assigned
- Top – The largest random number that could be assigned
Thus a formula like =RANDBETWEEN(1,1000) would generate random numbers between 1 and 1,000. You may be familiar with the RAND function, which generates random numbers between 0 and 1. Thus historically Excel users would craft a formula such as =RAND()*1000 to generate random numbers.
Both RANDBETWEEN and RAND are known as volatile worksheet functions in Excel, which means that the result changes every time you make any change to an Excel worksheet. To see this first hand you can put together some sample data as shown in Figure 1: