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.
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.
- To see the desired result, first select the entire range. Then, apply the following formula by using CTRL+SHIFT+ENTER.
=RAND()*50+50
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.
- Just enter the following formula in cell B5 to get the result shown in the picture below.
=RANDARRAY(6,6,500,700,FALSE)
Read More: How to Use Random Number Generator in Excel (5 Quick Ways)
Similar Readings
- Auto Generate Invoice Number in Excel (with 4 Quick Steps)
- Random Number Generator with Data Analysis Tool and Functions in Excel
- Random 5 Digit Number Generator in Excel (7 Examples)
- Random Number Generator in Excel with No Repeats (9 Methods)
- Generate Random Number from List in Excel (4 Ways)
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.
- 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.👇
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
- How to Generate Random 10 Digit Number in Excel (6 Methods)
- How to Generate Random Numbers Without Duplicates in Excel (7 Ways)
- Generate Random Number between Two Numbers in Excel (4 Ways)
- Excel VBA: Random Number Generator with No Duplicates (4 Examples)
- Random Number Generator with Normal Distribution in Excel (4 Methods)
- Generate Random Number with Mean and Standard Deviation in Excel