Manually Generating random numbers is tiresome work. However, we can produce random numbers using different Excel functions. In this article, we will discuss 2 methods for how to generate a random number between 0 and 1 in Excel.
Generate Random Number Between 0 and 1 in Excel: 2 Methods
We can generate random numbers within a range by 2 Excel functions. They are RAND, RANDARRAY, and RANDBETWEEN. But the RANDBETWEEN function returns integer random numbers only, so it does not suit this case.
1. Use Excel RAND Function to Produce Random Numbers Between 0 and 1
The RAND function returns a random number greater than or equal to 0 or less than 1, evenly distributed. The random numbers change on each recalculation.
To apply this function, follow the simple steps below.
Steps
- Go to Cell B4. (Or wherever you want!)
- Put the following formula.
=RAND()
- Now, press the Enter button and then drag the Fill Handle icon.
Thus, we generated random numbers between 0 to 1. If we need to generate numbers between other ranges, need to modify the formula.
2. Generate Random Number Using the RANDARRAY Function
The RANDARRAY function returns an array of random numbers.
In this section, we will use the RANDARRAY function to generate numbers in a range.
Steps
- Go to Cell B4 and put the following formula on that cell.
=RANDARRAY( , ,0, 1,FALSE)
Note:
The first two arguments define the rows and columns to form an array. We can skip or specify the row and column number. The 3rd and 4th arguments indicate the minimum and maximum values of the range. We put 0 and 1. In the last argument, we have two options integers and decimal. As we need decimal numbers, choose FALSE. Or you can leave this last argument. It’s FALSE by default.
- Then, press Enter and pull the Fill Handle icon downwards.
Create Random Integers Between Two Numbers in Excel
We already showed how to generate random numbers between 0 and 1. Now, we will generate random integers between a range. We will use the combination of the ROUND and RAND functions here.
The ROUND function rounds a number to a specified number of digits.
Steps
- Put the formula below on Cell B4.
=ROUND(RAND()*(5-2)+2,0)
- Press the Enter button and pull the Fill Handle icon.
Here, we simply generated integer numbers. If we remove the ROUND function from the formula, we will get random decimal numbers instead of integers.
Random Occurance of Pre-specified Numbers Between 0 & 1
We have learned to generate random numbers between 0 and 1 so far. In this section, we will show how to create a random repetition of priorly fixed random numbers. We are going to utilize the CHOOSE and RANDBETWEEN functions for that.
The CHOOSE function chooses a value or action to perform from a list of values based on an index number.
The RANDBETWEEN function returns a random number between the numbers you specify.
Steps
- Put the following formula on Cell B4.
=CHOOSE(RANDBETWEEN(1,4),0.3,0.1,0.8,0.5)
The RANDBETWEEN(1,4) indicates that there are 4 fixed random numbers to view. The arguments after the RANDBETWEEN function indicate the values those will view.
- Now, press the Enter button and drag the Fill Handle icon.
Things to Remember
Random numbers generated by Excel functions will change at every recalculation. If we need to use those values as a reference, we need to extract only the values after generating them once.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
In this article, we showed how to generate random numbers between 0 and 1. We also added a method to generate random integers. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.
<< Go Back to Random Number in Excel | Randomize in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!