Random Number Generator with Data Analysis Tool and Functions in Excel

Generating random numbers manually is a very time-consuming thing. With the help of Excel’s tools and functions, you can produce numbers in a very short time. In this article, you will learn how to utilize the Data Analysis Tool and 4 functions as a random number generator in Excel.


Download Workbook

You can download the free practice Excel workbook from here.


Implementing Data Analysis Tool and Functions as Random Number Generator in Excel

Following this section, you will learn how to utilize the Data Analysis Tool and the RAND, RANK.EQ, RANDBETWEEN and RANDARRAY function to generate random numbers in Excel.

1. The Data Analysis Tool as a Random Number Generator in Excel

You can generate random numbers with Excel’s Data Analysis Tool.
Look at the following picture. We will fill up this Random Numbers dataset with random numbers generated from the Data Analysis tool in Excel.

Dataset for Random Number Generator with Data Analysis Tool in Excel

The steps to execute that are given below.

Steps:

  • First, go to tab Data -> Data Analysis.

Random Number Generator with Data Analysis Tool in Excel

  • Then, from the Data Analysis pop-up box, select Random Number Generation.
  • Next, click OK.

Selecting Option for Random Number Generator with Data Analysis Tool in Excel

  • Subsequently, another Random Number Generation pop-up box will appear.
  • In the Number of Variables section, write the number of columns that you want to generate random numbers in. For instance, we want to generate random numbers in 2 columns, Column B and Column C. So, we wrote 2 in there.

Selecting Columns for Random Number Generator with Data Analysis Tool in Excel

  • After that, in the Number of Random Numbers section, write the number of random numbers you want to generate. For example, we want to generate 10 random numbers, so we wrote 10 in that box.

Selecting Numbers for Random Number Generator with Data Analysis Tool in Excel

  • There are 7 types of Distribution in random number generation. The types are:
    • Uniform: To generate numbers from a specified low value to a high value.
    • Normal: To generate numbers based on a specified range of Mean and Standard Deviation.
    • Bernoulli: To generate numbers that have the probability of success either 0 or 1.
    • Binomial: To generate numbers that have the probability of success for a number of trials.
    • Poisson: To generate numbers based on Lambda value, fraction equal to 1/Mean.
    • Patterned: To generate numbers that have a lower and upper limit, in a certain step and a fixed number of repetition periods for both the values and the sequence.
    • Discrete: To generate numbers that have a value and probability. The sum of the probability is generally 1. To display results, it usually uses two columns.

In our case, we selected Uniform distribution. You can select any distribution type according to your requirements.

Selecting Distribution for Random Number Generator with Data Analysis Tool in Excel

  • As a result, the Parameters group will show up. Insert the low value and the high value in the appropriate boxes. In our case, we will generate random numbers from 1 to 5, so we wrote 1 and 5 in the Between section.

Selecting parameters for Random Number Generator with Data Analysis Tool in Excel

  • Random Seed is usually used when we need to generate the same set of numbers. Random Seed keeps the data centred on the inserted values.
    For now, we are skipping this section.
  • Write the destination location in the Output Range. We want to generate a random number in Cell B5, so we selected that cell. Or you can manually write the cell reference address.
    You can even place the newly generated numbers in a new worksheet or even a new workbook. For that, you just have to simply check the New Worksheet Ply or the New Workbook option from the pop-up box.

Selecting location for Random Number Generator with Data Analysis Tool in Excel

  • After you have done all those procedures, now press OK.

Overview of Random Number Generator with Data Analysis Tool in Excel

Now, look at the following image to check the final outcome of all the steps that we executed.

Result of Random Number Generator with Data Analysis Tool in Excel

Finally, we have successfully generated random numbers from 1 to 5 in the specified location in our Excel dataset.

Read More: How to Generate Random Data in Excel (9 Easy Methods)


2. The RAND Function to Generate Random Numbers in Excel

Excel’s RAND function can be utilized in generating random numbers in Excel.
The RAND function returns a random number greater than or equal to 0 and less than 1. The numbers are evenly distributed, they change on every recalculation.

The steps to generate random numbers with the RAND function are given below.

Steps:

  • First, pick a cell to store the number. We picked Cell B5.
  • In that cell, write the RAND function.
=RAND()
  • Then, press Enter.

  • You will see there will be a random number generated in that cell.

Random Number Generator with Data Analysis Tool and RAND function in Excel

  • Now, drag the cell down to produce as many random numbers as you want in the worksheet.

By following these steps, you can successfully generate random numbers from 0 to less than 1 with the RAND function in Excel.

Result of Random Number Generator with Data Analysis Tool and RAND function in Excel

Read More: Excel Formula to Generate Random Number (5 examples)


3. The RANK.EQ Function as the Random Number Generator

You can also utilize the RANK.EQ function to produce random numbers in Excel. To produce random numbers with RANK.EQ, you have to have a source number list. As we have already generated random numbers in the range B5:B14 with the RAND function, so we will take that range as our source number list to generate random numbers in the range C5:C14 with the RANK.EQ function in Excel.

The RANK.EQ function returns the rank of a number in a list of numbers. Its size is relative to the other values in the list. If more than one value has the same rank, the top rank of that set of values is returned.

The steps to generate random numbers with the RANK.EQ function are given below.

Steps:

  • First, pick a cell to store the first number of a list of numbers. We picked Cell C5, the cell next to the first generated number from the RAND function. The RAND function generated the source range B4:B14.
  • In that cell, write the RANK.EQ function. While writing, you will notice the parameters that you need to input inside the parentheses of the function.
    • You need to pass a source number as the first parameter. In our case, it is the number from Cell B5, so we pass the cell reference of B5 in there.
    • After that, you need to pass the list of source numbers as the reference In our case, it is the range B5:B14.
    • The order parameter is optional, if you want your data to organize in Ascending or Descending format, then you should use this.

  • In our case, the formula now becomes like this:
=RANK.EQ(B5,$B$5:$B$14)
  • Then, press Enter.

  • You will see there will be a random number generated in that cell.

Random Number Generator with Data Analysis Tool and RANK.EQ function in Excel

  • Now, drag the cell down to produce the rank numbers based on the rank of the source number list in your worksheet.

Result of Random Number Generator with Data Analysis Tool and RANK.EQ function in Excel

Read More: Generate Random Number from List in Excel (4 Ways)


Similar Readings


4. RANDBETWEEN Function as Random Number Generator in Excel

The RANDBETWEEN function is another effective function to generate random numbers in Excel. It returns the random numbers between the specified numbers.

The steps to generate random numbers with the RANDBETWEEN function are given below.

Steps:

  • At first, pick a cell to store the number. We picked Cell B5.
  • In that cell, write the RANDBETWEEN While writing, you will notice the parameters that you need to input inside the parentheses of the function.
    • bottom means the lower value. We define 5 as the low value for our dataset.
    • top means the upper value. We declare 10 as the high value for our dataset.

  • In our case, the formula now becomes like this:
=RANDBETWEEN(5,10)
  • Then, press Enter.

  • You will see there will be a random number generated in that cell.

Random Number Generator with Data Analysis Tool and RANDBETWEEN function in Excel

  • Now, drag the cell down to produce random numbers between the numbers 5 and 10 in the worksheet.

Result of Random Number Generator with Data Analysis Tool and RANDBETWEEN function in Excel


5. The RANDARRAY Function to Generate an Array of Random Numbers

If you want to generate an array of random numbers, then Excel has the RANDARRAY function to get the task done successfully.
The RANDARRAY function returns an array of random numbers in Excel.

The steps to generate random numbers with the RANDARRAY function are given below.

Steps:

  • First, pick a cell to store the first number of an array of numbers. We picked Cell B5.
  • In that cell, write the RANDARRAY While writing, you will notice the parameters that you need to input inside the parentheses of the function.
    • You need to pass rows – how many rows will carry the randomly generated numbers. We want 5 rows to carry the numbers, so we pass 5 as the first parameter inside the parentheses of the function.
    • After that, you need to pass columns – how many columns will carry the randomly generated numbers. We want 2 columns to carry the numbers, so we pass 2 as the second parameter.
    • Next, you need to pass min – the minimum value. We want to generate numbers from 5, so 5 is the minimum value for our dataset.
    • Then, you need to pass max – the maximum value. We want to generate numbers till 10, so 10 is the maximum value for our dataset.

    • If you want the return result in the Decimal value then select FALSE, or if you want the result in the Integer value then select TRUE. We want our result in the Integer format, so we picked TRUE.

  • In our case, the formula now becomes like this:
=RANDARRAY(5,2,5,10,TRUE)
  • Then, press Enter.

Random Number Generator with Data Analysis Tool and RANDARRAY function in Excel

Lastly, you will get an array of random numbers from numbers 5 to 10 residing in 5 rows and 2 columns with the RANDARRAY function in Excel.


Conclusion

To conclude, this article showed you how to utilize the Data Analysis Tool and 4 functions as a random number generator in Excel. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.


Related Articles

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo