Generating of Random Numbers For Testing of Formulas

Do you need to generate random numbers to test formulas? Consider using the RANDBETWEEN function. The function can be used to create some random numbers for testing of formulas so that you don’t need to make them up.

The RANDBETWEEN function returns a random integer number between the numbers you specify. A new random integer number is returned every time the worksheet is calculated.

 Applies To: MS Excel 2003, 2007 and 2010

The RANDBETWEEN function allows you to generate a random, whole number inside of a range. The syntax is:=RANDBETWEEN(bottom,top)

The bottom parameter is the lowest number in the range you want to use, and top is the highest number in the range.

1.  Select cell A2 and type =RANDBETWEEN(1,100) and press Enter


When you press Enter, a random whole number between 1 and 100 is generated (in this instance, the number 32 is returned)

2.  Move your mouse to the bottom left hand corner of cell A2, click and hold on the AutoFill Handle, and drag it down to cell A7



You’ll note that the moment you release the AutoFill Handle, all cells generated a random number, including cell A2 – it has changed from 32 to 78. RANDBETWEEN will generate a new number each time you press Enter, or even when you use the UNDO or REDO functions (as long as you’re not altering any of the RANDBETWEEN cells)

3.  Select cell B2. Type in =A2*100 and press Enter


Again, when you hit Enter, you’ll notice that all values in cells A2 to A7 change to a new, random value.