Random Number Generator

Randomness has its pros and cons. However, many of us might be comfortable using fixed numbers buts Random numbers have its own importance. From different algorithms to gaming to hypotheses, random numbers are significantly used to generate the output.

For instance, you select random winners from a given list using the concept of random numbers, or you can also get a random list of numbers for data analysis or to choose random students to patriciate in any competition.

Generate Random Numbers using RANDBETWEEN function

RANDBETWEEN is an inbuilt Excel function that will quickly generate a set of random integer numbers between the two given numbers. Before moving forward, let's understand detailed information about this function.

What is RANDBETWEEN function?

"The RANDBETWEEN function returns a random integer number between the given set of numbers. Every time you run this function in your Excel worksheet, Excel automatically generates a new random number."

Syntax

Parameters

• Bottom (required)- This parameter represents the smallest integer number in between which the RANDBETWEEN function will return the output.
• Top (required)- This parameter represents the largest integer number in between which the RANDBETWEEN function will return the output.

Return

This function returns a random integer number between the specified set of numbers.

Example: Using RANDBETWEEN function generate a random whole number.

Follow the below given steps to generate a random whole number between two boundaries.

1. Select the cell at which you want the output of the RANDBETWEEN function to be calculated.
   
2. Start your formula using the equal to (=) sign followed by the RANDBETWEEN function. Your formula will be follows: =RANDBETWEEN
   
3. Enter the top and bottom integer numbers between which you want to generate a random whole number. Your formula will be follows: =RANDBETWEEN (10,1000)
   
4. Press enter key to fetch the output. As a result, Excel will generate a random number.
   
5. If you calculate the function again, you will notice this time Excel have generated another random number.
   

Generate Random Numbers using RAND function

Sometimes, while working we only want to deal in evenly distributed real random numbers between 0 and 1. These numbers are further used for analysis and calculations. In such cases, we can take leverage of the inbuilt RAND function. Before moving forward with examples, let's understand detailed information about this function.

What is RAND function?

"The RAND function returns a random real number greater than or equal to 0 and less than 1. This function returns a new random real number every time the user makes any changes in the Excel worksheet."

Suppose you wish to generate random integer numbers using the inbuilt RAND function, but you don't want to change the numbers every time the worksheet's formula cell is calculated. You can specify =RAND() in the formula bar and press F9 to change the formula to a random number, and the formula will be calculated automatically. It will return just a value.

Syntax

Parameters

NO PARAMETERS

Return

This function returns a random integer number between the specified set of numbers.

Example: Using RAND function generate a random whole number.

Follow the below given steps to generate a random whole number between two boundaries.

1. Select the cell at which you want the output of the RAND function to be calculated.
   
2. Start your formula using the equal to (=) sign followed by the RAND function. Your formula will be follows: =RAND
   
3. Since this function doesn't include any parameter, close the parenthesis and press enter to fetch the output. As a result, Excel will generate a random integer number between 0 and 1.
   
4. If you calculate the function again, you will notice this time Excel have generated another random number.
   

Note: The RAND function falls under the category of volatile Excel formula. Therefore, it would recalculate the formula even is the user do some minor changes in the worksheet. Make sure you have converted all the RAND function results to values.

Random Numbers using RANDARRAY function

In various situation you don't want just a single random value but an entire set of random values. In such cases, the RANDARRAY function is the perfect solution. let's understand detailed information about this function.

What is RANDARRAY function?

"The RANDARRAY function in Excel is an inbuilt function used to return an array of random numbers. You can customise the number of rows and columns to fill the random number, minimum and maximum values, and can choose whether you want to return whole numbers or decimal values."

Syntax

Parameters

• Rows (optional): This parameter represents the number of rows that RANDARRAY function return.
• Columns (optional): This parameter represents the number of columns to return.
• Min (optional): It signifies the minimum value for the random numbers.
• Max (optional): It signifies the maximum value for the random numbers.
• Whole_Number (optional): This parameter takes Boolean data type. If TRUE to return whole numbers, and FALSE to return decimal numbers.

NOTE: If all the parameter fields are left blank, this function will return a single random number with decimal places between 0 and 1 (it will work similarly to the RAND function).

Return

This function returns a random integer number between the specified set of numbers.

Example: Using RANDARRAY function generate a random whole number.

Follow the below given steps to generate a random whole number using the RANDARRAY function:

1. Select the cell at which you want the output of the RANDARRAY function to be calculated.
   
2. Start your formula using the equal to (=) sign followed by the RANDARRAY function. Your formula will be follows: = RANDARRAY(
   
3. To generate an array of 4 rows and 3 columns of whole random numbers between 6 and 14 you can use the below given formula.
   Your formula will be follows: = RANDARRAY(4,3,6,14,TRUE)
   
4. Press enter to fetch the output. As a result, Excel will generate a random number.
   

NOTE: The array will automatically resize if you change either the rows or columns parameters in the RANDARRAY formula. This is why they're known as dynamic arrays.

Generate Random Numbers using Analysis Tools Add-In

So far, we have covered different Excel functions that will quickly insert random numbers into your worksheet. But do you know there exists another method that can help us to generate random numbers without seeking the help of an Excel formula? It's Analysis Toolpak.

Excel comes with an Analysis Tool Pak add-in, but many times it's not pre-installed in your active worksheet. Don't worry, you just have to follow a few steps to install it before you can use it.

Install the Analysis Toolpak

Following are the steps to install the Analysis Tool Pak add-in to your Excel worksheet:

Step 1: From the Excel ribbon window click on the File tab.

Step 2: The following window panel will appear. Scroll down and click on the Options. Alternatively, you can also use the keyboard shortcut Alt, F, T directly from your worksheet to open the Options window.

Step 3: The Excel options dialog window will appear. From the left side of the pane, click on Add-Ins options.

• As a result, you notice the main window will change. From the bottom of the dialog window, look for the manage option. From the Manage drop down choose Excel Add-ins option and click on the Go button.
  
• The add-ins window will pop-up displaying all the available add-ins options for Excel. Click on the box for Analysis Tool Pak add-in and click on OK.
  
• Now go back to the Excel ribbon, and you will notice on the Data tab, there is now an extra group called Analysis with one button called Data Analysis.

Generate Random Numbers with the Analysis Toolpak

1. Go to the Excel ribbon menu-> click on Data tab-> from the analysis group click on the Data Analysis option.
   
2. This will display a pop-up window. Scroll down and select the Random Number Generation option and then click OK.
   
3. A new pop-up window will appear where you can enter your parameters to generate the random numbers.
   
4. There are several settings that can be customized.
   • Number of Variables: It represents the total columns that include random numbers that you want in the final table. If you don't specify any number, the output range that you have provided will be filled. all columns will be filled in the output range that you mention.
   • Number of Random Numbers: It represents the rows of random numbers that the user wishes to generate. If it is set empty, all total number of rows you mentioned in the output range will be filled.
   • Distribution:In this drop-down field you can select any distribution methods unlike discrete uniform or normal distribution. Depending on your selection, different options will become accessible in the Parameters section.
   • Parameters:Specify the data values to justify the selected distribution.
   • Random Seed:It is an optional field and will be the starting point for the algorithm to produce the random numbers. If you use the same seed again, it will produce the same random numbers.
   • Output Range:Here we have to specify the upper left cell of your worksheet where you want to create the table. If the variables parameter is left empty, you must specify an entire range. Note: If the selected range has existing data, in that case, it will be overwritten.
   • New Worksheet: This option will quickly insert a new worksheet within existing workbook and it will paste the output of the generated random number at Cell A1. You can customise the sheet name as well, if left blank a default name will be automatically generated.
   • New Workbook:This option will create a new workbook and paste and it will paste the output of the generated random number at Cell A1.
   We have filled the following in the above fields.
   
5. Press the OK button and Excel will insert the random number according to the selected options.
   
6. Notice that unlike the formula methods previously shown, these numbers are hardcoded and will not change when you refresh calculations in the workbook.

Generate Random Numbers with the help of VBA

VBA or Visual Basic for Applications is Microsoft's programming language for Excel that operates behind the front end of Excel. This application can also be used to generate random numbers.

Unlike the above Excel formulas, creating random numbers in VBA is not that simple, and you must also have programming knowledge to employ it.

Below given are the steps to generate random numbers using Excel VBA:

1. The first step is to open the VBA editor by clicking on the keyboard shortcut Alt + F11.
2. It will redirect you to the VBA window. From the left-pane you will find all the open workbooks and available sheets.
   
3. Next, we will insert a module in which we will write the programming. Therefore, from the top menu select Insert-> module. It will quickly add a module window to active worksheet.
   

4. Write the below given code to the active module.

Code:

Sub RandomNumber() 
MsgBox Rnd() 
End Sub

5. Press F5 to run the above code. As a result, a window pop-up will be displayed showing a random number. Press the OK button to return to the code module.

6. If you rerun the code, you will notice a different random number is returned this time. Every time you run this code, it will generate a number between 0 and 1 (it won't include the 0 or 1 values).

7. We can also integrate the Excel functions to generate a random number. The RND function can be used to produce the random numbers.

8. Write the below given code to the active module.

Code:

Sub RandomRndFunction() 
MsgBox Round((Rnd() * 7) + 3) 
End Sub

NOTE: In the RND parameter if you set the seed to a negative number or zero, this function will return the same random number every time you run the code.

9. Press F5to run the above code. As a result, a window pop-up will be displayed showing a random number. Press the OK button to return to the code module.

That all about Random numbers in this tutorial! Go ahead and give random numbers a try.