Not every user will have a need for random numbers in Excel. Most people work with fixed numbers and formulas and may have no need for random numbers to appear in their reports. Show
However, a random number generator does have a huge use when working with different scenarios on a set of data or when performing various statistical analyses. A financial model may use a stochastic simulation that is dependent on probabilities. The model may need to be run thousands of times, but with the random number generator providing the parameters of each simulation. Whatever your need for random numbers, Excel has several ways to generate them. In this post, I’ll show you all the methods you can use to insert random numbers into your workbooks. Generate Random Numbers with the RAND functionThe first way I will show you is the easiest way to generate random values in Excel. There is a very simple RAND function that requires no parameters and will generate a random number between 0 and 1. Syntax for the RAND Function
This function has no required or optional arguments. The function is always entered with an empty set of parenthesis. This function will generate a decimal random number between 0 and 1, but not including 0 or 1. Repeated values are possible but unlikely since the RAND function produces numbers from a continuous range of numbers. The values that are returned will follow a uniform distribution. This means that any number between 0 and 1 is equally likely to be returned. Generate Random Numbers Between Any Two NumbersA decimal number between 0 and 1 may not be too useful if you need numbers between 1 and 10. But you can use a simple formula involving the RAND function to generate random numbers between any two numbers.
In general, you can create a random number between X and Y by using the above formula.
For example, to generate numbers between 1 and 10 you can use the above formula. This multiplies the random number generated by 9 and then adds 1 to it. This will produce decimal numbers between 1 and 10. Generate Random Integer Numbers Between Any Two NumbersAnother possible need you may encounter is to generate random whole numbers between two given numbers. This can also be done using a simple formula.
In general, you can use the above formula to generate random integer numbers between two values X and Y.
For example, the above formula will create random integer numbers between 1 and 10. This is the same formula as before, but using the ROUND function to round to zero decimal places. You can copy this formula down the column on the spreadsheet, and if you keep pressing F9 to re-calculate, you will see various combinations of numbers from 1 to 10. Since the set of possible numbers is discrete, the random numbers generated may well be duplicated in the list, depending on what the minimum and maximum are of the range.
This also works for producing negative numbers. Suppose you need to generate random integer numbers between -3 and 4, then the above formula will be what you need. Multiplying the RAND function by 7 will produce random numbers between 0 and 7. Add -3 to the result and round to zero decimal places, and this will give the range of random numbers of -3 to 4. Generate Random Numbers using the RANDBETWEEN FunctionExcel has a useful function for generating random numbers within a range of an upper and lower number. This is easier to use than using the RAND function as it includes extra operators to arrive at your specific range. Syntax for the RANDBETWEEN Function
Both of these arguments are required. This function will produce random integer numbers between the bottom and top values. This function will also return the upper and lower limits as possible values as it’s not strictly between in this function. Example with the RANDBETWEEN Function
For example, if you wanted random numbers between -3 and 4, as in the previous example, you can use the above formula.
Generate Random Numbers with the RANDARRAY FunctionUsually, it’s the case that you don’t want just a single random value but an entire set of random values. The RANDARRAY function is the perfect solution for this. It will populate a range of cells with an array of random numbers, which can be very powerful. This function is only available on the Microsoft 365 version of Excel. Syntax for the RANDARRAY Function
All the arguments are optional for this function. If no parameters are included, you will get a single random number with decimal places, in the same way as the RAND function. Example with the RANDARRAY Function
To generate an array of 4 rows and 3 columns of whole random numbers between 6 and 14 you can use the above formula. This will produce an array of values. Notice the blue border around the numbers? These are all produced from a single formula!
If you do not put a minimum or maximum value, the default of 0 to 1 will be used. The minimum value must be less than the maximum value otherwise there will be a #VALUE! error. 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 with the Analysis Tools Add-InThere is another method that can be used to insert random numbers without using a formula. You can use an add-in to create random numbers. Excel comes with an Analysis Tool Pak add-in, but you will need to install it before you can use it. Install the Analysis ToolpakHere are the steps to install the Analysis Tool Pak add-in.
Generate Random Numbers with the Analysis ToolpakClick on the Data Analysis button in the Analysis group. This will display a pop-up window. Scroll down and select the Random Number Generation option and then click OK. A new pop-up window will appear where you can enter your parameters to generate the random numbers. There are several settings that can be customized.
Press the OK button and Excel will insert the random number according to the selected options. 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 VBAVBA (Visual Basic for Applications) is the programming language that sits behind the front end of Excel, and this can also be used to generate random numbers. However, it is more complicated than simply entering a formula into a cell in Excel, and you do need some programming knowledge to use it. To open the VBA editor, use the Alt + F11 keyboard shortcut. In the left-hand pane of the window (Project Explorer), you will see the workbooks that are open (including add-ins) and the sheets available. On the menu at the top of the window, click on Insert and then click on Module. This will add a module window to the current spreadsheet. Paste or add the following code to the module.
Press F5 to run this, and a message pop-up will appear in Excel with a random number displayed. Press OK and you will return to the code window. Run the code again and a different random number will be displayed. The random number will be between 0 and 1, but will not include the values of 0 or 1. You can also give the Rnd function a parameter, which is a seed for the starting point of the algorithm to produce the random numbers. If the seed value is set to a negative number or zero, then the same random number will be displayed each time. Using VBA functions, you can emulate all the functionality of the front-end methods that have been covered in this article.
For example, if you wanted to generate whole random numbers between 3 and 10, then you would use the following above code. This code multiplies the random number, by 7, and then adds 3 to it, and then rounds to zero decimal places. Suppose that you then wanted to display your random numbers in the grid. You can do this with the following code.
This code uses a For Next loop to iterate 5 times through the random number calculation and enter the results in a column of cells starting at cell A1. Remember that any data already there will be overwritten, and there is no warning or undo feature available. Save any previous work beforehand!
There is also a VBA function called Randomize. You can use this before the Rnd function to reset the seed value to the timer event, or to any parameter given. Generate Random Numbers without Duplicates or RepeatsYou may well have a situation where you want to generate a range of random numbers, but you do not want to see any duplicate values appearing. You may want to select 3 random numbers between the numbers from 1 to 10, but where each of the 3 selected numbers is unique. You could generate random numbers with the RANDBETWEEN function and then use the Excel function Remove Duplicates from the ribbon, but this still may not give you all the numbers required. There are several possible solutions available. Solution with RANK.EQ and COUNTIF FunctionsIf you don’t have access to the RANDARRAY function in Excel, then you can use a combination of RANK.EQ and COUNTIF to get unique random numbers. You can create your random numbers using RANDBETWEEN and then use a formula in the next column to rank them thereby giving you a randomly sorted sequence from 1 to 10.
In cell B2, enter the above formula. Copy this formula down so that there are 10 rows of random numbers going down to cell B11. You will notice that some numbers may be duplicated and some are not shown at all. You can then use the RANK.EQ function to rank them so as to create a sequence from 1 to 10 but that is sorted randomly.
In cell C2, enter the above formula. Note that there are absolute references used (the $ signs) so that formula references stay fixed as you copy the formula down. Copy this formula down to cell C11, and this will display all the numbers between 1 and 10, but in random order. To explain this formula in more depth, it uses two functions RANK.EQ and COUNTIF.
The RANK.EQ function returns the rank of a number within an array of numbers.
The COUNTIF function counts the number of cells based on a given criterion. In this case, it is counting how many times a given random number has appeared in the list. For each random number the RANK.EQ function will determine its ranking position relative to the other random numbers. But if the random numbers contain duplicates, then they will create a tied ranking. The COUNTIF function will compensate for any ties in the ranking and will add one to the rank for each time the random number has previously appeared. This creates a unique ranking where ties don’t get the same rank. Since this rank is based on a set of random numbers the result is the same as randomizing a list of numbers from 1 to 10. Now, if you only want 5 non-repeating numbers, you only need to take the first 5 from the ranking list. Solution with VBAYou could also use VBA to generate a string of random numbers from 1 to 10 without duplicates.
This code iterates through values from 1 to 5, generating a random number between 1 and 10 each time. It tests the random number to check if it has already been generated. This is done by concatenating successful numbers into a string and then searching that string to see if the number has already been used. If it has been found, then it uses the label Repeat to go back and re-generate a new number. This is again tested that it has not already been used. If it is a new number, then it is added to the sheet. Solution with Dynamic ArraysIf you have dynamic arrays in Excel, then there is a single formula method to avoid repeating values. Suppose you want to return 5 numbers from the sequence 1 to 10. You want each number selected to be unique. This can be done using a combination of the SEQUENCE, SORTBY, RANDARRAY, and INDEX functions.
The above formula creates a sequence of numbers from 1 to 10. It then sorts them in a random order using the SORTBY function and sorting on a column of random numbers generated by the RANDARRAY function. The effect is to sort the sequence in random order. Now if you want to get 5 random and unique numbers you only need to take the first 5 numbers from the randomly sorted sequence. This is exactly what the INDEX function does! This part of the formula will return the first 5 numbers from the randomly sorted sequence. ConclusionThere are several ways to generate random numbers in Excel. Whether you need whole numbers, decimals, or a range of random numbers with an upper and lower limit, the facility is available. Excel is extremely versatile on this topic. However, bear in mind that these numbers are pseudo-random numbers generated by an algorithm. Although the random number generator passes all the tests of randomness, they are not true random numbers. To be a truly random number, it would have to be driven by a random event happening outside the computer environment. For most purposes of constructing general simulations and statistical analysis, the Excel random number generator is considered fit for the purpose. Have you used any of these methods for generating random numbers in Excel? Do you know any other methods? Let me know in the comments below! About the AuthorJohn is a Microsoft MVP and qualified actuary with over 15 years of experience. He has worked in a variety of industries, including insurance, ad tech, and most recently Power Platform consulting. He is a keen problem solver and has a passion for using technology to make businesses more efficient. Are commonly used to control the number of times a loop iterates?Counter variables are commonly used to control the number of times a loop iterates. In this loop, the count variable is both declared and initialized in the initialization expression. If the variable is used only in the loop, it makes sense to define it in the loop header. This makes the variable's purpose clearer.
What structure can execute a set of statements only under certain circumstances?A decision (or selection) structure is a control structure that can execute a set of statements and perform a specific action only if a certain condition exists.
What structure provides one alternative path of execution?What is a single alternative decision structure? A decision structure that provides a single alternative path of execution. If the condition that is being tested is true, the program takes the alternative path.
|