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:
About David Ringstrom, CPA
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.