Create Random Numbers

May 12, 2022 - by Bill Jelen

Problem: I want to create a range of random numbers or letters.

Strategy: You use the RANDBETWEEN function. This function will return a random integer between lower and upper limits. Here are some examples:

=RANDBETWEEN(1,100) for random integers between 1 and 100.
=RANDBETWEEN(100,500)/100 for random prices between $1.00 and $5.00
=RANDBETWEEN(-20,20)/100 for random growth from 80% to 120%/
For random capital letters, use: =CHAR(RANDBETWEEN(65,90)).
For a random item from a list stored in B7:I7, use =INDEX($B$7:$I$7,RANDBETWEEN(1,8)).

Five ways to return random items. Integers between 1 and 100. Dollars and cents between 1 and 5. A percent between -20% and 20%. Random capital letters. Random items from a list stored in a range. — Figure 467. Generate random values.

Additional Details: The last bullet point shows off an interesting and undocumented feature of INDEX. Normally, you would specify =INDEX(range,row,column). This would mean that you would have to specify =INDEX(B7:I7,1,RANDBETWEEN(1,8)). However, when you range is exactly one row tall, Excel will use the second argument as a column number instead of a row number.

Alternate Strategy: Excel also offers the RAND function, which will return a decimal between 0 and 0.9999999. Instead of using the formula =RANDBETWEEN(1,10), you could use =INT(RAND()*10)+1.

Additional Details: Every time you press F9 or enter a new value in the worksheet, the random numbers will change. You might want to change the formulas to values to freeze the random numbers. To do this, you select the range of random numbers, press Home, Copy, and then select Home, Paste dropdown, Paste Values to convert formulas to numbers.

Gotcha: These are actually pseudo-random numbers. If you are performing complex modeling involving millions of numbers, patterns may emerge.

This article is an excerpt from Power Excel With MrExcel

Title photo by Tyler Easton on Unsplash