Generate Random Number in Excel with Decimals (3 Methods)

Get FREE Advanced Excel Exercises with Solutions!

This article illustrates 3 ways to generate a random number with decimals in Excel. It also highlights the ways you can produce arrays of whole numbers or text strings. The following picture highlights the purpose of this article. Have a quick look to learn how to do that.

Generate Random Number in Excel with Decimals


Generate Random Number with Decimals in Excel: 3 Ways

1. Generate Random Number with Decimals Using Excel RAND Function

You can use the RAND function in Excel to generate random decimal numbers. It returns a random number greater than or equal to 0 and less than 1.

  • First, enter the following formula in cell B5.
=RAND()
  • Then, drag the Fill Handle icon to populate the entire range (B5:G10) with the formula.

  • Alternatively, you can select the entire range first. Then type the formula.

  • After that, press CTRL+SHIFT+ENTER to get the desired result as follows.

  • Now, you can use Cell Formatting to limit the decimals to a certain number (4) of digits. Alternatively, you can nest the function inside the ROUND function to get the job done as shown in the following picture.
  • Now imagine you want to get random decimal numbers between 50 (lower limit) and 100 (upper limit). Here, the difference between the numbers is 50 (upper limit – lower limit). Then, you can use a generic formula as follows.
=RAND()*Difference+Lower_Limit
  • To see the desired result, first select the entire range. Then, apply the following formula by using CTRL+SHIFT+ENTER.
=RAND()*50+50

Generate Random Decimal Numbers with Excel RAND Function


2. Produce Random Decimal Numbers with Excel RANDARRAY Function

You can also use the RANDARRAY function in Excel to produce random numbers with decimals. This function returns an array of random numbers with a specified number of rows and columns between two specified numbers. Unfortunately, this function is available to Office365 users only.

RANDARRAY([rows], [columns], [min], [max], [integer])
  • Just enter the following formula in cell B5 to get the result shown in the picture below.
=RANDARRAY(6,6,500,700,FALSE)

Produce Random Decimal Numbers with Excel RANDARRAY Function


3. Random Number with Decimals Using RANDBETWEEN & RAND Functions in Excel

Using the combination of the RANDBETWEEN and RAND functions in Excel can be another alternative for this purpose. The RANDBETWEEN function returns random numbers between two specified numbers.

RANDBETWEEN(bottom, top)
  • First, select the entire range where you want to generate the random decimal numbers.
  • Then type 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()

After that, you will see the following result.👇

Random Decimal Numbers with RANDBETWEEN & RAND Functions

 


Produce Random Whole Numbers in Excel

You can use the RANDARRY function to generate random whole numbers too. Just enter the following formula in cell B5 as shown below.

=RANDARRAY(6,6,1000,2000,TRUE)

  • Alternatively, you can use the RANDBETWEEN function for that purpose. First, 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.
  • Always remember to press CTRL+SHIFT+ENTER to enter the array formulas.
  • Be careful to correctly use the lower and upper limits when you want to get the random decimal numbers between two negative numbers. For example, you should use -500 as the maximum and -1000 as the minimum and not the other way around.

Conclusion

Now you know how to generate a random number with decimals in Excel using formulas. Please let us know if this article has helped you with your problem. You can also use the comment section below for further queries or suggestions. Do visit our ExcelDemy blog to explore more about Excel. Stay with us and keep learning.


<< Go Back to Random Number in Excel | Randomize in Excel Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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