MS Excel has an inbuilt function i.e. RANDBETWEEN which returns a random number between the numbers you specify. The random numbers that this function generates can be repetitive.I found two ways to guarantee that there are no duplicates in a list of random numbers.
Approach 1 : Ranking random integers
Suppose you want 50 non-repeating numbers between 1 and 50.
1. Use RAND() function in cell B6:B55
2 In cell D6, type =RANK(B6,B$6:B$55) and paste it down till cell D55
Download this workbook and practice what you learn.
Approach 2 : Big Daddy Formula
Specify lower limit i.e. 1 in cell J3 and upper limit i.e. 50 in cell J4.
Hit CTRL+ SHIFT + ENTER to confirm this formula. If done correctly, Excel will automatically place curly braces {...} around the formula.
Download this workbook and practice what you learn.
How to modify
Approach 1 : Ranking random integers
Suppose you want 50 non-repeating numbers between 1 and 50.
1. Use RAND() function in cell B6:B55
2 In cell D6, type =RANK(B6,B$6:B$55) and paste it down till cell D55
Download this workbook and practice what you learn.
Approach 2 : Big Daddy 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 :
Hit CTRL+ SHIFT + ENTER to confirm this formula. If done correctly, Excel will automatically place curly braces {...} around the formula.
Download this workbook and practice what you learn.
How to modify
Suppose you want 100 non-repeating numbers between 5 and 105.
1. Specify lower limit i.e. 5 in cell J3 and upper limit i.e. 105 in cell J4.
2. In F column, paste the formula down till row F105.
About Author:
Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he has worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and Human Resource.
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