Random Numbers

Is it possible to generate random numbers in Excel?

By Neale Blackwood

Yes. Often it is useful, when setting up a spreadsheet, to use random numbers before you start using “live” numbers. There are two functions that will generate random numbers.

The first is the RAND function. It produces a random number (fraction) between zero and one. You then multiply this fraction by a number to arrive at your random numbers. Typically you also use the ROUND function to arrive at your required precision. Eg =ROUND(RAND()*1000,0) will supply a random whole number between 0 and 1000. You could then copy the formula to other cells to generate more random numbers.

The second is the RANDBETWEEN function which is standard in Excel 2007, but is part of the Analysis ToolPak Add-in in Excel 2003 and earlier versions (see note below on how to install the Analysis ToolPak). RANDBETWEEN is easier to use and allows you to specify a lower and upper limit to the random numbers generated. E.g. =RANDBETWEEN(1000,2000) will generate random whole numbers between 1000 and 2000.

Notes on random numbers
Each time the workbook calculates, the random numbers generated will change. To capture random numbers, first copy the range. Then use Paste Special > Values on top of the range to convert the formula to values. The numbers generated are not unique. If you were trying to generate 6 Lotto numbers between 1 and 45 you could get a number two or more times (the odds are 1 in 9 that you will get duplicated numbers). To generate unique random numbers you will need to use a macro.

Note: Analysis ToolPak installation. Click the Tools menu, then click Add-ins, then check Analysis ToolPak (which should be at the top of the list) then click OK. You may need the Office installation CD. Once the ToolPak is installed a Data Analysis Option is added to the Tools Menu or Data Ribbon.

Extras

Excel 2007

Even though RANDBETWEEN is a standard formula in Excel 2007 it is advisable to install the Analysis Toolpak. To do that in Excel 2007 click the round Office button and then click the Excel Options button. Click the Add-in option on the left hand side. Click the Go button at the bottom of the screen and tick the Analysis Toolpak click OK.