Generating Random Number Between Two Numbers in Excel

If you are looking for how to generate a random number between two numbers in Excel, you’ve come to the right place. There are several ways to generate a random number between two numbers in Excel. In this article, we’ll talk about the 7 quickest steps of generating random number between two numbers in Excel. The purpose of this post is to teach you how to use Excel to produce a random number that falls between two given integers. Excel comes equipped with a variety of built-in functions that, when used, may produce a random number between two other numbers in a very short amount of time.

In addition, the Analysis ToolPak and VBA are both viable options for accomplishing this. In this section, you will learn how to generate random numbers using both integers and decimals. Therefore, without further ado, let’s get the conversation started. Here is an example of our result.

📁 Download Excel File

Download this practice book.

Learn to Generate Random Number Between Two Numbers in Excel with These 7 Ways

We will learn how to make a random number between two numbers in Excel in this article. There are different built-in functions in Excel that can quickly come up with a random number between two numbers. We can also do this with the help of the Analysis ToolPak and VBA. Here, you’ll learn how to make random numbers that are both whole numbers and decimals. So, let’s get right to it and start talking. Here is our dataset. We see some names of employees of a certain company. We want to assign them as team leaders to various groups. Here we will do that randomly.

Excel comes with a function called RANDBETWEEN that makes it easy to make a random integer number between two numbers. The RANDBETWEEN function generates a random integer that falls anywhere between the two values passed to it. When a worksheet is opened for editing or closed, a new random number will be computed automatically, and the result from RANDBETWEEN will be displayed automatically.

The RANDBETWEEN function requires two inputs: the bottom and the top values. The random number’s lower bound is represented by the bottom, and the random number’s upper bound is represented by the top. In the range of integers that could be returned, RANDBETWEEN takes into account both the highest and lowest possible values.

Let’s go through these steps to find out more.

⬇️⬇️ STEPS ⬇️⬇️

• At first, we select cell C6. Then we put the formula below:

• Then we press Enter and drag down the formula. The final result will be like this.

📕 Read More: 5 Ways to Generate Random Number Between 0 and 1 in Excel

Random decimal numbers are made by the RAND function. But we can make numbers that are integers if we put the RAND function inside the ROUND function. You can use RAND function to generate random number, when you do not want to change numbers each time you calculate a cell. For that, enter =RAND() in formula bar, press F9 and the formula will change random number.

This is an option if you don’t want the numbers to change every time the cell is calculated. The calculation will only provide you with a value after using the formula. The ROUND function takes a number and rounds it up or down to a certain number of digits. First, we are going to insert the specific formula into a cell; then we are going to copy down the formula to the last cell of our dataset. Thus we are going to find our result.  Steps are given below.

⬇️⬇️ STEPS ⬇️⬇️

• At first, we select cell C6. Then we put the formula below:

• Then we press Enter and drag down the formula. The final result will be like this.

📕 Read More: 8 Ways to Make Random 4 Digit Number Generator in Excel

The RANDARRAY function is ideal for creating random integers inside a given array or range. To add another set of numbers, we’ll make use of a new column. First we are going to insert the specific formula to a cell, then we are going to copy down the formula to the last cell of our dataset. Thus we are going to find our result. A random number array is what the RANDARRAY function gives back to the calling function. You have the option of returning either whole numbers or decimal values, as well as specifying the number of rows and columns that should be filled in. The steps are easy and given below.

⬇️⬇️ STEPS ⬇️⬇️

• At first, we select cell C6. Then we put the formula below:

• Then we press Enter and the final result will be like this.

• Lastly, cell C6 will store the formula. So, if you delete the value from Cell C6, the whole array will go away.

📕 Read More: 7 Ways to Create Random 5 Digit Number Generator in Excel

Using the RANK.EQ and COUNTIF functions together, we can come up with unique sequence numbers. RANK.EQ gives back the number’s position in a list of numbers. Its size is relative to the other values in the list. If two or more values have the same rank, the highest rank is returned. RANK.EQ will always assign the same rank to duplicate numbers. On the other hand, the ranks of the numbers that follow are impacted when duplicates of earlier numbers are present. For instance, in a list of integers arranged in ascending order, if the number 10 appears twice and has a rank of 5, then the number 11 would have a rank of 7 if it were the next number in the list (no other number will have a rank of the 6). This formula uses the COUNTIF function to find each name of one Column in another Column.

But this can be used in some situations. Let’s say that some of the numbers in Group 1 are already there. Now, you need a random number sequence starting with 1. In that case, the following method can be used. First, we are going to insert the specific formula into a cell, and then we are going to copy down the formula to the last cell of our dataset. Thus, we are going to find our result. The steps are easy and given below.

⬇️⬇️ STEPS ⬇️⬇️

• At first, we select cell D6. Then we put the formula below:

• Then we press Enter and drag down the formula. The final result will be like this.

📕 Read More: 6 Ways to Generate Random 10-Digit Number in Excel

To generate random decimal numbers between 0 and 1, we use the RAND function. If we want to use RAND to produce a random number, but we don’t want the numbers to change every time the cell is computed, you can type =RAND() in the formula bar and then press F9 to change the formula to a random number. This works even if you don’t want the numbers to change every time the cell is calculated. The calculation will be complete, and all you will be left with is a value. Follow the steps below to know more.

⬇️⬇️ STEPS ⬇️⬇️

• At first, we select cell C6. Then we put the formula below:

• Then we press Enter and drag down the formula. The final result will be like this.

By utilizing the Data Analysis ToolPak, we can generate random numbers between two numbers in Excel. You must first install this ToolPak in your Excel program to accomplish that. If you are tasked with creating complex statistical or engineering analyses, you will be able to save time and operations by using the Analysis ToolPak. You are in charge of giving each analysis the necessary data and parameters. The tool will then compute and display the results in an output table using the pertinent numerical or engineering macro functions.

In addition to tables, certain technologies can provide graphical representations of the data. Data analysis functions can only be used simultaneously solely on a single worksheet at a time. When you run a data analysis on a set of grouped worksheets, the first worksheet will display the results, and the remaining worksheets will display empty prepared tables. Before moving on to the other worksheets in the data analysis process, each worksheet requires a new calculation of the analysis tool.

Here the overall steps of this particular method are given below.

⬇️⬇️ STEPS ⬇️⬇️

• First, we go to the File menu.

• Then we click on Options.

• In the Excel Options window, we will see Add-ins. We click on here.

• Here in the Add-ins, we click on Go…

• A new box called Add-ins will be open. We click on Analysis ToolPak and then OK.

• Now we go to the Data tab and select Data Analysis.

• In the Input Range box, we choose Random Number Generation.

• Now, we put the column numbers into the Number of Variables field and the row numbers into the Number of Random Numbers.
• In the Distribution field, we choose “Uniform.”
• In the Parameters field, we enter the minimum and maximum values. There are 10 and 20.
• And finally, in the Output Range field, we type the starting cell with a dollar sign ($). Here, we’ve written $C$56.
• Now we press OK.

• Here is our result.

📕 Read More: 8 Easy Tricks to Generate Random Data in Excel

Now we will use VBA to generate a random number. Before talking about the method, first, we have enabled Developer Tab on our Ribbons. You can follow the link to see the process of enabling the Developer Tab by clicking here. The Developer tab is a function that is featured in Microsoft Excel; however, it is typically buried because the default setting for Excel is to hide it.

Users are granted the ability to construct VBA applications, design forms, generate macros, import and export XML data, and perform a variety of other tasks using the tab.

In order for the Developer tab to become visible on the toolbar of the Excel window, it must first be enabled in the Options section of the File menu. Evaluation of the financial performance of organizations, simulation of predicted results, and even comparison of investment possibilities are all under the purview of finance specialists. They are able to carry out more advanced functions in Excel, such as designing forms, writing code, building macros, as well as importing and exporting XML file formats when the Developer tab is activated.

Let’s follow the steps below:

⬇️⬇️ STEPS ⬇️⬇️

• Let’s press ALT + F11 to bring up the VBA Window. You can also do this by going to the Developer tab and clicking on Visual Basic.

• Then we choose Module from Insert from the menu.

• This will bring up the window for the VBA Module. Here is where we will put our code.

Public Function RandNumber(p As Long, q As Long, Optional Decimals As Integer)
Application.Volatile
Randomize
If IsMissing(Decimals) Or Decimals = 0 Then
    RandNumber = Int((q + 1 - p) * Rnd + p)
Else
    RandNumber = Round((q - p) * Rnd + p, Decimals)
End If
End Function

• Now we save and run the code.

• Later we select cell C6. Then we put the formula below.

• Then we press Enter and drag down the formula. The final result will be like this.

📕 Read More: 4 Ways to Introduce Random Number in Excel with Decimals

📄  Important Notes

🖊️  In Excel, the ribbon is used to access various commands. You can change a lot of things in the options dialogue window, like the ribbon, formulas, proofing, saving, and so on. So the appearance of worksheets on your device could be different from ours.

🖊️  RANDBETWEEN does a recalculation every time a worksheet is accessed or its contents are modified.

🖊️  RANDBETWEEN will return integers. You can generate random decimal numbers by calling the RAND function.

🖊️  In RANDARRAY function, cell C6 will store the formula. So, if you delete the value from Cell C56, the whole array will go away.

🖊️  A practice workbook is given so that you can practice yourself.

🖊️  All these exercises and tutorials are done on Microsoft Office 365. Some functions may be unavailable in previous versions.

🖊️  At the side of each Excel worksheet file, there is space where they can practice.

📝  Takeaways from This Article

📌  Readers will be able to generate a random number in excel between two numbers with various methods.

📌  They will be able to use RANDBETWEEN Function to make a random number between two numbers in Excel.

📌  Besides, they will be able to apply ROUND and RAND Functions Together to pick a random number between two numbers in Excel to create a random number in Excel between two numbers.

📌  Moreover, the readers will be able to implement Excel RANDARRAY Function to make Excel generate a random number between two numbers.

📌  They will also be able to combine RANK.EQ & COUNTIF Functions to pick a random number between two numbers in Excel

📌  They can insert RAND Function too to create a random number in Excel between two numbers.

📌  The readers will be able to enable Analysis ToolPak in Excel to generate a random number between two numbers.

📌  Finally, they can implement VBA for the same purpose to make Excel generate a random number between two numbers.

Conclusion

That concludes today’s session. These are the methods how to generate random number between two numbers in Excel. We are hopeful that this article will be extremely beneficial to you. I have faith that utilizing these approaches will make completing your chores a breeze for you. In addition, the practice book may now be found at the beginning of the article, thanks to our addition. You can download it to find out more information. In conclusion, I’d want to invite anyone who may still have questions or comments to post them in the space below for others to see. Don’t forget to leave your thoughts and questions in the comments section and explore our website, ExcelDen.com, the best Excel solutions provider.

Related Article

• 4 Steps to Auto Generate Invoice Number in Excel