|
The Randbetween() function enables us to generate random numbers between two numbers in Excel, however; if we want to generate multiple random numbers that fall between two certain numbers and we don’t want any repeating numbers we will run into issues with Randbetween(). The reason is that each cell that contains the formula creates a random number and the cells are not in any way linked to each other and chances are that a certain number might be repeated and therefore Randbetween() does not guarantee uniqueness when it is being applied to multiple cells. Here is an example, as you could see in the screenshot below, I inserted the =Randbetween (1,49) formula into six cells and after refreshing the sheet just a few times a duplicate number was generated (i.e. the number 33 was repeated twice). We don’t want that to happen.  That said, here is a very neat alternative to Randbetween() that guarantees uniqueness in the random number generation for a series of cells. Let’s say we want to generate 6 unique random numbers between 1 and 49. STEP 1] Use the rand() function to create 49 random numbers. I have applied the formula to cells A1 through A49.  STEP 2] Use the formula below instead of the Randbetween() formula to generate random numbers between 1 and 49. This formula references the $A$1:$A$49 range and this range needs to be reference locked before being dragged to the right. =MATCH(LARGE($A$1:$A$49,1),$A$1:$A$49,0) The second argument in the LARGE statement gets incremented as the formula is dragged to the right. for example, the value for the second number will have LARGE($A$1:$A$49,2) and that number will keep on incrementing. Here is the final outcome once the formula is correctly applied to the full range and the six unique random numbers are generated.  Now no matter how many time you refresh the spreadsheet you will NEVER get any duplicates. The trick behind this method is that the MATCH formula is picking up the row number for the six largest numbers in the $A$1:$A$49 range and the number order in the $A$1:$A$49 range is completely random and the numbers are always unique. This means that the largest numbers are not arranged in any particular sequence and this guarantees that six random and unique numbers will be generated when this range is referenced through the MATCH formulas.
0 Comments
|
