In this article, we will learn to generate a random number in Excel between two numbers. In Excel, there are different built-in functions that can generate a random number between two numbers very quickly. We can also use the Analysis ToolPak and VBA for this purpose. Here, you will learn to generate both integer and decimal random numbers. So, without any delay, let’s start the discussion.
Generate Random Number between Two Numbers in Excel: 4 Ways
To explain the methods, we will use a dataset that contains some employees’ names. We need to assign random numbers to each employee in Column C.
1. Generate Integer Random Number between Two Numbers in Excel
In the following sub-methods, we will generate an integer random number between two numbers in Excel. We can use different default functions for this purpose.
1.1 Use RANDBETWEEN Function
Excel has a default RANDBETWEEN function that can generate only an integer random number between two numbers easily. The RANDBETWEEN function has two required arguments. The first argument denotes the lower limit and the second argument denotes the upper limit. Let’s follow the steps below to know more.
STEPS:
- First of all, select Cell C5 and type the formula:
=RANDBETWEEN(10,20)
- After that, press Enter to see the result.
Here, the RANDBETWEEN function generates a random number between 10 & 20.
- Finally, drag the Fill Handle down to see results like the picture below.
Note: The generated numbers will change automatically if any changes are made in the sheet. So, to avoid that, copy the generated numbers and paste only values in other cells.
Read More: How to Generate Random Data in Excel
1.2 Apply ROUND and RAND Functions Together
The RAND function generates random decimal numbers. But we can produce integer numbers by putting the RAND function inside the ROUND function. Suppose, you need to generate integer random numbers between K & M numbers where M is the greater number. Then, the formula will be:
=ROUND(RAND()*M-K+K,0)Let’s follow the steps below to generate numbers.
STEPS:
- Firstly, select Cell C5 and type the formula:
=ROUND(RAND()*10+5,0)
- Secondly, hit Enter to see the result.
Here, the formula generates integer random numbers between 5 to 15. That is why we have multiplied 10 with the RAND function. 10 is the subtraction of 15 & 5.
- In the end, use the Fill Handle to generate more random numbers.
1.3 Implement Excel RANDARRAY Function
If you need to generate integer random numbers in an array or range, then, RANDARRAY is the perfect function for you. Here, we will use another column where we will insert another group of numbers.
The syntax of the RANDARRAY function is given below:
RANDARRAY([rows],[columns],[min],[max],[integer])Suppose, you want to generate an array that has 5 rows and 4 columns, the minimum number in that will be 10 and the maximum will be 50. Then, the formula will be RANDARRAY(5,4,10,50,TRUE).
Let’s pay attention to the steps below to learn more.
STEPS:
- In the beginning, select Cell C5 and type the formula:
=RANDARRAY(6,2,10,20,TRUE)
- In the following, hit Enter to see the result.
- Interestingly, Cell C5 will store the formula. So, if you erase the value of Cell C5, then, the whole array will disappear.
1.4 Combine RANK.EQ & COUNTIF Functions
We can compute unique sequence numbers using RANK.EQ and COUNTIF function together. But this can be used for a certain case. Suppose, we already have numbers in Group 1 and some of them are duplicates. Now, you want a random sequence number starting from 1. In that case, we can use the following method.
Let’s observe the steps below for more.
STEPS:
- In the first place, select Cell D5 and type the formula:
=RANK.EQ(C5,$C$5:$C$10)+COUNTIF($C$5:C5,C5)-1
- After that, hit Enter to see the result.
Here, the RANK.EQ function ranks Cell C5 in the range C5:C10. The COUNTIF function counts the number of Cell C5 in the range $C$5:C5.
- Finally, use the Fill Handle to see results in the rest of the cells.
2. Random Decimal Number Generation between Two Numbers
In the second case, we will generate a random decimal number between two numbers in Excel. We can use the RAND and the RANDARRAY functions for this purpose. The RAND function produces decimals between 0 and 1. Again, we will use the same dataset. So, without further ado, let’s jump to the sub-methods.
2.1 Insert RAND Function
To generate random decimal numbers between 0 and 1, use the RAND function. Follow the steps below to know more.
STEPS:
- To begin with, select Cell C5 and type the formula below:
=RAND()
- After that, press Enter to see the result.
- Lastly, drag down the Fill Handle to produce numbers in other empty cells.
2.2 Use RANDARRAY Function
We have already used the RANDARRAY function to generate integer random numbers. The RANDARRAY function can also be used for producing decimal numbers. For that, we need to make a small change. Suppose, you want to generate an array with decimals that has 4 rows and 5 columns. The decimal numbers should be in the range of 20 to 30. Then, the formula will be:
RANDARRAY(4,5,20,30,FALSE)Here, we have written FALSE in the last argument instead of TRUE. To explain the method, we will use the dataset below.
Now, let’s follow the steps to learn more.
STEPS:
- In the first place, select Cell C5 and type the formula:
=RANDARRAY(6,2,5,15,FALSE)
Here, this formula will create an array that will have 6 rows and 2 columns. The decimal numbers should be between 5 to 15.
- After that, hit Enter to see the result.
Read More: Generate Random Number in Excel with Decimals
3. Enable Analysis ToolPak in Excel to Generate a Random Number between Two Numbers
You can also use the Analysis ToolPak to produce random numbers. For that, you need to add it first. Sometimes, the ToolPak may not be available in the first place. In that case, you need to download it. In the following steps, we will describe the whole process.
STEPS:
- First of all, select the File tab in the ribbon.
- Secondly, select Options from the bottom-left of the screen.
- Thirdly, select Add-ins and then, select Excel Add-ins.
- After that, select Go. It will open the Add-ins window.
- Check Analysis ToolPak and click OK.
- Next, go to the Data tab and select Data Analysis. This will open the Data Analysis window.
- In the Data Analysis window, select Random Number Generation.
- The Random Number Generation will look like the picture below.
- Now, put the column numbers in the Number of Variables field and row numbers in the Number of Random Numbers field.
- Select Uniform in the Distribution field.
- Enter the lower and upper limits in the Parameters field. We have 10 & 20.
- And lastly, enter the starting cell with the Dollar ($) sign in the Output Range field. We have typed $C$5 here.
- Finally, click OK to see results like the picture below.
Read More: Random Number Generator with Data Analysis Tool and Functions in Excel
4. Create Random Number between Two Numbers with Excel VBA
VBA also gives us the opportunity to produce both integer and decimal random numbers with a single user-defined function. In this method, we will use the same dataset. So, without any delay, let’s follow the steps below.
STEPS:
- In the beginning, select Visual Basic from the Developer tab. It will open the Visual Basic window.
- After that, select Insert and then, Module in the Visual Basic window. The Module window will appear.
- Now, type the code in the Module window:
Public Function RandNum(x As Long, y As Long, Optional Decimals As Integer)
Application.Volatile
Randomize
If IsMissing(Decimals) Or Decimals = 0 Then
   RandNum = Int((y + 1 - x) * Rnd + x)
Else
   RandNum = Round((y - x) * Rnd + x, Decimals)
End If
End Function
Here, x is the lower limit and y is the upper limit. The UDF name is RandNum. It will take 3 arguments as input. We can write RandNum(lower,upper,decimal point). The third argument indicates the decimal points. If you want no decimal point in the generated numbers, then, you need to type 0 in the third argument.
- Press Ctrl + S to save the code and then, close the Visual Basic window.
- Next, select Cell C5 and type the formula below:
=RandNum(10,20,0)
- Hit Enter to see the result.
Here, the formula generates a random number between 10 to 20 without any decimals.
- Once again, use the Fill Handle to see results in all cells.
- To generate numbers to 2 decimal points, type the formula below:
=RandNum(10,20,2)
- After that, press Enter.
- Finally, drag down the Fill Handle to see results like the picture below.
Things to Remember
The most important thing to remember is that the numbers we generated using the above methods are not hardcore. To make them constant, copy the generated numbers by pressing Ctrl + C and then, paste only values in any other cell.
Download Practice Book
Download the practice book here.
Conclusion
We have demonstrated 4 easy methods to Generate a Random Number in Excel between Two Numbers. We have used different methods and also discussed a method with VBA. I hope these methods will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. You can download it to learn more. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.
