Method 1 – Generate Random Number with Decimals Using Excel RAND Function
The RAND function returns a random number greater than or equal to 0 and less than 1.
- Enter the following formula in cell B5.
=RAND()- Drag the Fill Handle icon to populate the entire range (B5:G10) with the formula.
- Alternatively, you can select the entire range first, then enter the formula.
- Press CTRL+SHIFT+ENTER to return the results as below.
- Cell Formatting can limit the decimals to a certain number of digits (4 in this instance). Alternatively, you can nest the function inside the ROUND function as in the following picture.
- If you also want random decimal numbers between 50 (lower limit) and 100 (upper limit), use the following formula.
=RAND()*Difference+Lower_Limit
- To see the desired result, select the entire range, and apply the following formula by using CTRL+SHIFT+ENTER.
=RAND()*50+50
Method 2 – Produce Random Decimal Numbers with Excel RANDARRAY Function
- Enter the following formula in cell B5 to get the result shown below.
=RANDARRAY(6,6,500,700,FALSE)
Method 3 – Random Number with Decimals Using RANDBETWEEN & RAND Functions in Excel
RANDBETWEEN(bottom, top)
- Select the entire range.
- Enter the following formula and then press CTRL+SHIFT+ENTER. The -1 in the formula prevents the RANDBETWEEN function from returning the maximum number (800). This way the final output won’t be greater than the maximum limit.
=RANDBETWEEN(400,800-1)+RAND()The dataset is now filled with random generated numbers.
Method 4 – Produce Random Whole Numbers in Excel
- Enter the following formula in cell B5 as shown below.
=RANDARRAY(6,6,1000,2000,TRUE)
- Alternatively, you can use the RANDBETWEEN function for this purpose. Enter the following formula in cell B5. Then, drag the Fill Handle icon to apply the formula to the entire range of cells.
=RANDBETWEEN(30000,50000)
Things to Remember
- The RANDARRAY function is only available in Office365.
- Press CTRL+SHIFT+ENTER to enter the array formulas.
- Be careful when setting the lower and upper limits between two negative numbers. For example, you should use -500 as the maximum and -1000 as the minimum, not the other way around.
<< Go Back to Random Number in Excel | Randomize in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
