Generate Random Number in Excel with Decimals (3 Methods)

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


Download Practice Workbook

You can download the practice workbook from the download button below.


3 Ways to Generate Random Number with Decimals in Excel

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

Read More: Excel Formula to Generate Random Number (5 examples)


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

Read More: How to Use Random Number Generator in Excel (5 Quick Ways)


Similar Readings


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

Read More: Generate Random Number Between 0 and 1 in Excel (2 Methods)


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.


Related Articles

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo