Random Number Generator between Range in Excel (8 Examples)

In this article, I will discuss the random number generator between a range in Excel. Often, while doing statistical and financial analysis, you might have to use a random number generator. Whatever the purpose is, Excel has several ways to generate random numbers. Let’s have a look at those ways.

Random Number Generator between Range in Excel: 8 Suitable Examples

1. Use Excel RAND Function to Generate Number between a Range

You can use the RAND function as a random number generator. Usually, this function creates random numbers between 0 to 1.

Steps:

• First. write the below formula in Cell B5. Hit Enter. As expected, you will get a number between 0 to 1.

• Now, drag the Fill Handle (+) tool to get a list of numbers between the RAND function’s range.

• Finally, here is the list of numbers.

• Besides, you can set the range of the random numbers using the RAND For example, I want to get numbers between 0 and 6. Then type the below formula in Cell B5 and press Enter.

• As before, drag down the Fill Handle (+) and get the below result.

📌 Convert Formula Results to Values:

Now, there is a problem with the above formula. The RAND function is a Volatile Function. The numbers we get from the function will continuously change on recalculation. So, to avoid that change we have to convert the result of the above formula to values. To do that, follow the below steps.

Steps:

• First, select the resulting list we have got, and press Ctrl + C.

• Next, from Excel Ribbon, go to Home > Paste. Now click on the Paste Values icon (See the screenshot).

• As a result, we got the numbers as values below. Now, these values won’t change on recalculation.

2. Apply RANDBETWEEN Function as Random Number Generator in a Range

Let’s use the RANDBETWEEN function to get a list of random numbers. Using this function, you can specify the top and bottom numbers of your range. For example, we want to have random numbers between 10 and 50.

Steps:

• Type the below formula in Cell B5. Consequently, we will get the following result after hitting Enter.

• Upon using the Fill Handle tool, the following is our list of random numbers.

Similar to the RAND function, if necessary, make sure you convert the result of the RANDBETWEEN formula to values. This is because the RANDBETWEEN function is also a Volatile Function in Excel.

3. Use RANK.EQ and RAND Functions as Unique Number Generator between a Range

Usually, the RAND function returns unique numbers between a range. Still, to check the repetition of resulting random numbers, we can use the RANK.EQ function.

Steps:

• First, get a random number list using the RAND function.

• Then convert the list to values using the Paste Values option (Described in Method 1).
• Now, type the below formula in Cell C5.

• Press Enter.

• Now if you put any duplicate values in Column B, Column C will indicate it by showing duplicate integers to the corresponding RAND values.

4. Insert RANDARRAY Function as Random Number Generator in Excel

In Excel 365, we can use the RANDARRAY function as a random number generator. Syntax of the RANDARRAY function is mentioned below for your better understanding.

RANDARRAY([rows],[columns],[min],[max],[whole_number])

Suppose, you want to create a random number array between the range of 10 and 20, containing 5 rows and 2 columns, and I want to have whole numbers, then follow the below procedure.

Steps:

• Type the below formula in Cell B5. Press Enter and you will get an array (outlined as blue color) containing expected random numbers.

5. Excel ROUND and RAND Functions Combination as Random Number Generator in a Range

Now I will use the ROUND function along with the RAND function to get a random number list between 0 and 20.

Steps:

• First, write the following formula in Cell B5 and hit Enter. As a consequence, you will get the list of random numbers within the specified range.

Here, the result of the RAND formula is multiplied by 19 and then 1 adds to it. Later, the ROUND function will round the decimal number to 0 decimal places.

6. Use Analysis Toolpak Add in to Generate Random Numbers between a Range

We will use Excel add-ins to generate a random number list. Follow the below steps to do the task.

Steps:

• First, go to the File tab from Excel Ribbon.

• Next, go to the Options.

• Then Excel Options window will show up. Go to the Add-ins menu, now make sure Excel Add-ins are selected in the field: Manage. Click on the Go button.

• The Add-ins window will appear. After that, put a tick on the Analysis Toolpak and click OK.

• As a result, the Data Analysis option is added under the Data tab of Excel Ribbon. Now, click on the Data Analysis option.

• The Data Analysis dialog will pop up. Choose the Random Number Generation option and click OK.

• Put values on the below fields (see the screenshot) and click OK. For Example, I want to generate a random number list within the range of 10 to 50.

• Finally, we got the below result.

7. Apply VBA as Random Number Generator between Range in Excel

You can use VBA as a random number generator in Excel. Let’s see how to create a random number using VBA and show it both on the message box and worksheet.

7.1. Generate Random Number Using VBA and Return the Result in the Message Box

Let’s assume I want to get a random number between 0 and 13. Here are the steps involved in the process.

Steps:

• First, go to the corresponding worksheet and right-click on the sheet name, and then select the View Code option.

• As a result, the VBA window will appear. Write the below code on the Module.

Sub RandomNumber()

    MsgBox Round((Rnd() * 10) + 3)

 End Sub

• Run the code by pressing the F5 key or clicking on the run icon (see the screenshot).

• Upon running the code, you will get the below result in a message box.

7.2. Create Random Number Using VBA and Display in Excel Worksheet

For instance, if you want to get a random number (whole number) list between 3 and 10 then follow the below steps.

Steps:

• Go to the corresponding Excel sheet, right-click on the sheet name, and click on the View Code option to bring up the VBA window.
• Type the below code in the Module.

Sub RandomNumberEx1()

Dim N As Integer
For N = 1 To 5
    ActiveSheet.Cells(N, 1) = Round((Rnd(10) * 7) + 3, 0)
Next N

End Sub

• After that, Run the code.
• The below list will appear in the Excel sheet.

8. Random Number Generator without Duplicates (RANDBETWEEN, RANK.EQ & COUNTIF Functions)

Most of the time the RANDBETWEEN function returns the random number list containing duplicates. So, we will combine the RANK.EQ and COUNTIF function to get the unique random numbers.

Steps:

• First, I have created a random number list between 1 and 10 inserting the below formula in Cell B5.

• Press Enter.

• Then type the below formula in Cell C5 and hit Enter to get a random number list containing unique numbers between 1 to 10.

🔎 How Does the Formula Work?

➤ RANK.EQ(B5,$B$5:$B$13)

This part of the formula returns {5}. Here, the RANK.EQ function returns the rank of a number in a list of numbers.

➤ COUNTIF($B$5:B5,B5)

Now, this part of the formula returns {1}. Here the COUNTIF function counts the number of cells within $B$5:B5, that meet the specified condition.

➤ RANK.EQ(B5,$B$5:$B$13)+COUNTIF($B$5:B5,B5)-1

Finally, the formula returns {5}.

Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.

Conclusion

In the above article, I have tried to discuss several methods for random number generators between a range in Excel. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.

<< Go Back to Random Number in Excel | Randomize in Excel | Learn Excel