Random Number Generator between Range in Excel (8 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.
=RAND()

Use Excel RAND Function to Generate Number between a Range

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

Use Excel RAND Function to Generate Number between a Range

  • Finally, here is the list of numbers.

Use Excel RAND Function to Generate Number Between a Range

  • 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.
=RAND()*5+1

Use Excel RAND Function to Generate Number Between a Range

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

Use Excel RAND Function to Generate Number Between a Range

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

Use Excel RAND Function to Generate Number Between a Range

  • 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.
=RANDBETWEEN(10,50)

Apply RANDBETWEEN Function as Random Number Generator in a Range

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

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

  • Then convert the list to values using the Paste Values option (Described in Method 1).
  • Now, type the below formula in Cell C5.
=RANK.EQ(B5,$B$5:$B$13)
  • Press Enter.

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

  • 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.
=RANDARRAY(5,2,10,20,TRUE)

Insert RANDARRAY Function as Random Number Generator in Excel


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.
=ROUND(RAND()*19+1,0)

Excel ROUND and RAND Functions Combination as Random Number Generator in a 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:

Use Analysis Toolpak Add in to Generate Random Numbers Between a Range

  • Next, go to the Options.

Use Analysis Toolpak Add in to Generate Random Numbers Between a Range

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

Use Analysis Toolpak Add in to Generate Random Numbers Between a Range

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

Use Analysis Toolpak Add in to Generate Random Numbers Between a Range

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

Use Analysis Toolpak Add in to Generate Random Numbers Between a Range

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

Use Analysis Toolpak Add in to Generate Random Numbers Between a Range

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

Use Analysis Toolpak Add in to Generate Random Numbers Between a Range

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

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

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

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

 End Sub

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

  • 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

Create Random Number Using VBA and Display in Excel Worksheet

  • 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.
=RANDBETWEEN(1,10)
  • Press Enter.

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

  • Then type the below formula in Cell C5 and hit Enter to get a random number list containing unique numbers between 1 to 10.
=RANK.EQ(B5,$B$5:$B$13)+COUNTIF($B$5:B5,B5)-1

🔎 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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hosne Ara
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo