Nashid
In this lesson, I’ll demonstrate 5 Ways to generate random number between 0 and 1 in Excel. Moreover, these techniques will enable you to solve the problem that led you here. However, you will master several crucial Excel features and functions during this article which will come in extremely handy for any assignment using Excel.
📁 Download Excel File
The sample worksheet that was used during the discussion is available for download below.
Concept Behind Generating Random Number in Excel
Excel’s random number generator, like all the other computer systems, generates quasi or pseudo-random numbers using mathematical algorithms. This implies that Excel-generated random numbers are, in principle, foreseeable. The RAND and RANDBETWEEN functions in Excel generate pseudo-random integers based on the Uniform distribution. This distribution is also commonly referred to as the rectangle distribution. This is a sort of distribution in which there is a chance that a random variable will take any one of the many potential values at any given time. For example, if we toss a single die each of the six potential toss results (1, 2, 3, 4, 5, 6) is equally probable.
Learn to Generate Random Number Between 0 and 1 in Excel with These 5 Easy Solutions
Here, in this article, we will learn how to generate random number between 0 and 1 in Excel using different approaches. To be exact, I’ve provided a total of 5 methods down below.
1. Using RAND Function to Generate Random Number Between 0 and 1
Here, we are going to demonstrate generating random number between 0 and 1 using the RAND function. This function by default generates a random number greater than or equal to 0 and less than 1 if not added anything. Follow the instructions below.
⬇️⬇️ STEPS ⬇️⬇️
- First, select a cell and copy the following formula.
=RAND()
- Now press Enter to get the random number.
📕 Read More: Generating Random Number Between Two Numbers in Excel
2. Using RANDBETWEEN Function
Here, we are going to use the RANDBETWEEN function to generate random number between 0 and 1. This function generates only whole numbers, no float numbers so we are going to add some logic to it. Follow the instructions below.
⬇️⬇️ STEPS ⬇️⬇️
- First, copy down the following formula in a cell.
=RANDBETWEEN(0,1000)/1000
- Now press Enter to get the random number.
📕 Read More: 7 Ways to Create Random 5 Digit Number Generator in Excel
3. Create Random Array Containing Number Between 0 and 1 in Excel
In this part, we are using the RANDARRAY function which generates random numbers.
You may set the minimum and maximum values, and the number of rows and columns as well as receive whole numbers or decimal values as per your demand. Here, the syntax is:
RANDARRAY(Row_numb, Column_numb, Bottom_value, Top_value, Decimal/Integer)
Follow the instructions below.
⬇️⬇️ STEPS ⬇️⬇️
- First, copy the following formula in a cell.
=RANDARRAY( 15,1,0, 1,FALSE)
- Press Enter to get the results.
📕 Read More: 4 Ways to Introduce Random Number in Excel with Decimals
4. Randomly Select Specific Numbers Between 0 and 1
In this section, we are going to use two different functions to create a formula for generating random number between 0 and 1 from some specific numbers. We are going to use CHOOSE and RANDBETWEEN functions. The CHOOSE function retrieves a value from an array at a specific location or index and the RANDBETWEEN function generates random integer numbers. Follow the instructions below.
⬇️⬇️ STEPS ⬇️⬇️
- First, copy down the following formula in a cell.
=CHOOSE(RANDBETWEEN(1,7),0.37,0.16,0.82,0.59,0.45,0.73,0.81)
🔨 Formula Breakdown
👉 RANDBETWEEN(1,7)
The RANDBETWEEN function here returns a random value from 1 to 7.
👉 CHOOSE(RANDBETWEEN(1,7),0.37,0.16,0.82,0.59,0.45,0.73,0.81)
The CHOOSE function returns the nth value from its given array. The n here is the value that this part RANDBETWEEN(1,7) returns.
- Press Enter to get the random number.
- Now copy down the formula along the column for the rest of the results.
📕 Read More: 8 Ways to Make Random 4 Digit Number Generator in Excel
5. Using VBA Code
Here, we are going to use Microsoft’s VBA feature to generate random numbers between 0 and 1. We will be developing a code that will return us our desired random values. Follow the instructions below.
⬇️⬇️ STEPS ⬇️⬇️
- First, you need to enable Developer tab on ribbon in Excel.
- Now go to the Visual Basic option from the Developer tab of the ribbon.
- After that, a new window will pop up. Select Module from the Insert tab.
- Now copy the following code.
Sub Generating_random_number_VBA()
Dim a As Long
a = 10
For a = 1 To a
ActiveCell.Value = Rnd()
ActiveCell.Offset(1, 0).Select
Next a
End Sub
- Save the code as macro enabled workbook.
- After that, select the top cell of the table.
- Now Go to Visual Basic from the Developer tab.
- Click The Run button.
- Or you can also go to Macros from the Developer.
- After that, click Run.
- The result will be like this.
📕 Read More: 6 Ways to Generate Random 10-Digit Number in Excel
📄 Important Notes
🖊️ You can use F9 to transform the formula into a value.
🖊️ Use shortcuts to reduce the time for instance F4 for absolute value.
🖊️ While writing formulas carefully see the suggestions and don’t forget the commas and parentheses.
🖊️ Be careful while defining criteria to be accurate as the dataset.
🖊️ As this is an xlsm file which means it’s a macro-enabled file. If you download this make sure to unblock it by going to the file properties and unticking Block before using.
🖊️ For unique random numbers create more than you need then use the Duplicate Remover for Excel to cut the duplicates out.
📝 Takeaways from This Article
📌 The article demonstrated different functions to generate random numbers between 0 and 1 in Excel.
📌 In the first section, we showed how to use the RAND function in excel.
📌 Then, we demonstrated different cases like specifying some numbers and randomly choosing them, We used RANDBETWEEN, RANDARRAY, and CHOOSE functions to generate random values between 0 and 1 in Excel.
📌 We also created an array of random numbers that range from 0 to 1.
📌 Finally, we used a VBA code to demonstrate the advanced solution.
Conclusion
Firstly, I hope you were able to use the techniques I demonstrated in this Generate Random Numbers Between 0 and 1 in Excel lesson. As you can see, there are a lot of options on how to do this. However, decide deliberately on the approach that best addresses your circumstance. Most importantly I advise repeating the steps if you become confused in any of the steps if you get stuck. However, practice on your own after taking a look at the Excel file in the practice workbook I’ve provided above because practice makes a man perfect. Above all, I earnestly hope you can use it. Please share your experience with us in the comment box about how your experience was throughout the article and what we need to improve or add to make things better for you folks. However, if you have any problem regarding Excel spit it in the comment box. The Excelden crew is always available to answer your questions. In conclusion, keep educating yourself by reading. For more Excel-related articles please visit our website Excelden.com.
