# Generate Random Number between Two Numbers in Excel (4 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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

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.

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.

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

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

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

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mursalin Ibne Salehin

Hi there! This is Mursalin. I am currently working as a Team Leader at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I manage and help the writers to develop quality content in Excel and VBA-related topics.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF