This article shows how to generate unique random numbers in an interval in Excel.
Microsoft Excel includes a built-in function called RANDBETWEEN that generates a random number within a specified range. However, the numbers generated by this function can sometimes repeat. In this article, we will cover two different methods to ensure that a list of random numbers does not contain any duplicates.
Method 1 : Rank random integers
Follow the steps below to generate non-repeating random numbers between 1 and 50.
- Enter the RAND() function in cells B6 to B55.
- In cell D6, input the formula =RANK(B6,B$6:B$55), and drag it down to cell D55.
Method 2 : Array Formula
Specify lower limit i.e. 1 in cell J3 and upper limit i.e. 50 in cell J4.
Copy the following formula into cell F6 and paste it down :
=IFERROR(LARGE(ROW(INDIRECT($J$3&":"&$J$4))*NOT(COUNTIF($F$5:F5,ROW(INDIRECT($J$3&":"&$J$4)))),RANDBETWEEN(1,$J$4-$J$3-ROW(A1)+2)),"")Hit CTRL+ SHIFT + ENTER to confirm this formula. If done correctly, Excel will automatically place curly braces {...} around the formula.
Download the working workbook.
How does the formula work?
- The formula uses the INDIRECT function along with cell references J3 and J4 to create a dynamic range.
- The ROW function generates an array of numbers corresponding to the range created in step 1.
- The COUNTIF function checks if each number in the array already exists in the cells above in column F.
- The NOT function inverts the results of the COUNTIF function.
- The product of the array and the inverted results is passed to the LARGE function.
- The LARGE function returns the kth largest value from the array, where k is the position of the number in the sorted array.
- If an error occurs, the formula falls back to the RANDBETWEEN function to generate a random number.
How to modify the formula
Suppose you want 100 unique numbers between 5 and 105.
- Enter the lower limit, 5, in cell J3 and the upper limit, 105, in cell J4.
- In column F, copy the formula down to row F105.
thank you so much
ReplyDeleteThank you, saved me some headache time.
ReplyDeletethanks
ReplyDeleteI tried the second method in Excel 2003, but the iferror function is not available there. Have you got an equivalent for Excel 2003?
ReplyDelete