5 Ways to Generate Random Number Between 0 and 1 in Excel

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.

Method 1

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()

excel random number between 0 and 1 Using RAND function.

  • Now press Enter to get the random number.

📕 Read More: Generating Random Number Between Two Numbers in Excel

Method 2

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

excel random number between 0 and 1 using RANDBETWEEN function

  • Now press Enter to get the random number.

📕 Read More: 7 Ways to Create Random 5 Digit Number Generator in Excel

Method 3

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)

excel random number between 0 and 1 using RANDARRAY function

  • Press Enter to get the results.

RANDARRAY function result for excel generating random number between 0 and 1

📕 Read More: 4 Ways to Introduce Random Number in Excel with Decimals

Method 4

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)

excel random number between 0 and 1 using CHOOSE and RANDBETWEEN function

🔨   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.

excel random number between 0 and 1 using CHOOSE and RANDBETWEEN function results.

excel random number between 0 and 1 results

📕 Read More: 8 Ways to Make Random 4 Digit Number Generator in Excel

Method 5

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 ⬇️⬇️

Seecting Visual BASics to use the code to excel random number between 0 and 1

  • After that, a new window will pop up. Select Module from the Insert tab.

Creating new module in Visual Basic

  • 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

excel random number between 0 and 1 VBA code

  • Save the code as macro enabled workbook.
  • After that, select the top cell of the table.

Selecting a blank cell.

  • Now Go to Visual Basic from the Developer tab.

Selecting Visual Basic option from Developer Tab

  • Click The Run button.

excel random number between 0 and 1 VBA code

  • Or you can also go to Macros from the Developer.

Selecting Macro option from Developer Tab

  • After that, click Run.

Running VBA Macro code

  • 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.


Related Articles

(Visited 33 times, 1 visits today)
Nashid

Nashid

Hello there. I am Nashid, an Engineer. I Love music, traveling, sports and gaming. I have recently grown interest in DATA SCIENCE and wish to build a career over it. Excel is one of the greatest modern digital creation of human. Life simply gets easier. My Articles are meant to lead you to that easier life with efficiency. If you need any kind of help regarding my articles even anything else try contacting me on social platforms of which I've provided. Keep learning. Wish me luck and Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo