Formula Friday - Random Number Generator

Do you need to quickly produce a random number?  Excel has two simple formulas that will quickly generate a random number. 

1. RAND
• The first option is to use the RAND formula.  This formula will generate a number greater than or equal to 0 and less than 1. 
• To use this formula, enter '=RAND()' in the cell where you want the random number

• Press Enter
• You will now see a random number

 

• Note: Each time the spreadsheet is recalculated, the number will change

2. RANDBETWEEN
• The second option is to use the RANDBETWEEN formula.  With this formula, a random number will be generated between 2 numbers that you identify.
• Note: The result from this formula will be an integer
• To use this formula, enter 'RANDBETWEEN(Number1,Number2)
• Number1 is the lowest number
• Number2 is the highest number
• Press Enter
• You will now see a random number
• Note: Each time the spreadsheet is recalculated, the number will change
• Example 1
• I am looking for a number between 1 and 20
• In cell A1, enter RANDBETWEEN(1,20)

• Press Enter
• An integer between 1 and 20 is generated

 

 

Once you have the formula in place, if you want to regenerate the random number, you can press F9 or click "Calculate Now" on the Formula tab in the Calculation section.

 

 

As I explained with the Sum formula, there are multiple ways to insert the formulas into the spreadsheet.

1. Type the formula in as described above
2. Use the "Insert Icon" function and type in either RAND or RANDBETWEEN

 

3. On the Formulas tab, click on the Math & Trig drop down in the Function Library section and scroll down to find either RAND or RANDBETWEEN

 

Example 2

• In this example I want a dollar amount between 1 and 10.  In this example, I am going to use a combination of the two formulas above as I want a number with decimals.  If I only wanted an integer, then I would just use the RANDBETWEEN formula.
• Insert  =RANDBETWEEN(1,10)+RAND() in the cell

• Press Enter
• Now Excel will generate a number between 1 and 10 and a number between 0 and 1 and add them together

 

Excel ya later!