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)

Generate Integer Random Number between Two Numbers in Excel

  • After that, press Enter to see the result.

Generate Integer Random Number between Two Numbers in Excel

Here, the RANDBETWEEN function generates a random number between 10 & 20.

  • Finally, drag the Fill Handle down to see results like the picture below.

Generate Integer Random Number between Two Numbers in Excel

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)

Generate Integer Random Number between Two Numbers in Excel

  • Secondly, hit Enter to see the result.

Generate Integer Random Number between Two Numbers in Excel

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.

Generate Integer Random Number between Two Numbers in Excel


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.

Generate Integer Random Number between Two Numbers in Excel

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)

Generate Integer Random Number between Two Numbers in Excel

  • In the following, hit Enter to see the result.

Generate Integer Random Number between Two Numbers in Excel

  • Interestingly, Cell C5 will store the formula. So, if you erase the value of Cell C5, then, the whole array will disappear.

Generate Integer Random Number between Two Numbers in Excel


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

Generate Integer Random Number between Two Numbers in Excel

  • After that, hit Enter to see the result.

Generate Integer Random Number between Two Numbers in Excel

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.

Generate Integer Random Number between Two Numbers in Excel


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

Random Decimal Number Generation between Two Numbers

  • After that, press Enter to see the result.

Random Decimal Number Generation between Two Numbers

  • Lastly, drag down the Fill Handle to produce numbers in other empty cells.

Random Decimal Number Generation between Two Numbers


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.

Random Decimal Number Generation between Two Numbers

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)

Random Decimal Number Generation between Two Numbers

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.

Random Decimal Number Generation between Two Numbers

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.

Enable Analysis ToolPak in Excel to Generate Random Number between Two Numbers

  • Secondly, select Options from the bottom-left of the screen.

Enable Analysis ToolPak in Excel to Generate Random Number between Two Numbers

  • Thirdly, select Add-ins and then, select Excel Add-ins.
  • After that, select Go. It will open the Add-ins window.

Enable Analysis ToolPak in Excel to Generate Random Number between Two Numbers

  • Check Analysis ToolPak and click OK.

Enable Analysis ToolPak in Excel to Generate Random Number between Two Numbers

  • Next, go to the Data tab and select Data Analysis. This will open the Data Analysis window.

Enable Analysis ToolPak in Excel to Generate Random Number between Two Numbers

  • In the Data Analysis window, select Random Number Generation.

Enable Analysis ToolPak in Excel to Generate Random Number between Two Numbers

  • The Random Number Generation will look like the picture below.

Enable Analysis ToolPak in Excel to Generate Random Number between Two Numbers

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


Related Articles

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo