Generate Random Number in Excel with Decimals (3 Methods)

 

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

Generate Random Decimal Numbers with Excel RAND Function


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)

Produce Random Decimal Numbers with Excel RANDARRAY Function


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.

Random Decimal Numbers with RANDBETWEEN & RAND Functions

 


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!
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo