generate distinct random numbers from using only formulas and not VBA.

I have to generate five distinct random numbers from 0 to 50 using only formulas and not VBA. How should I do it? excel random excel-formula share|improve this question asked Oct 28 '12 at 19:20 user1593760 1314
	add comment

closed as off topic by brettdj, Kjuly, brenjt, mnel, Kshitij Mehta Oct 29 '12 at 4:08

Questions on Stack Overflow are expected to relate to programming within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

2 Answers

activeoldestvotes

up vote4down voteaccepted

Assuming A1 is blank or a text header you can use this "array formula" in A2 =SMALL(IF(COUNTIF(A$1:A1,ROW(INDIRECT("1:51"))-1)=0,ROW(INDIRECT("1:51"))-1),INT(RAND()*(51-ROWS(A$2:A2)+1)+1)) confirm with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar, now copy to A6 That will generate 5 integers from 0 to 50 without repeats

How do I generate an array of random numbers (that don't repeat) in excel

How do I generate an array of random numbers (that don't repeat)?

I know how to use RAND and RANDBETWEEN to generate single random numbers, but I'm looking for a way (w/ Excel 2007) to generate, as an example, six random numbers between 101 and 133, none of which repeat (i.e., I don't want two instances of 114).

Yea, I can do it manually with 6 cells of RANDBETWEEN . . . . . then copy & paste-value (to a separate set of cells) . . . . . then sort & look for duplicates . . . . . then repeat until I get no duplicates . . . . . but I may do this a number of times, and I'm wondering if there is a more elegant way of accomplishing this.

Would be nice to have a RANDBETWEENARRAY(101, 133, 6, 0), where the 3rd parameter is the size of the array, and the 4th parameter is 0=no repeats & 1=repeats allowed.

 
•  
 
•  
 
•  

Found this helpful2

Answer

Andrea Jones - All About Re... replied on 

You could use an array formula to achieve this:

 

Enter your heading in cell A1

Enter the formula =RANDBETWEEN(101,133) in cell A2

Enter this array formula in cell A3:

=LARGE(ROW($101:$133)*NOT(COUNTIF($A$2:A2,ROW($101:$133))),RANDBETWEEN(1,(133+2-101)-ROW(A2)))

(press CTRL+SHIFT+ENTER to enter this as an array formula)

Now copy cell A3 down for as many rows as you require values and they should all be unique.

You can substitute the 101 and 133 in the above formula for any maximum and minimum values you want to use.

 
•  
 
•  

• All Replies (8)

Found this helpful1

beingpavelbure replied on 

Would be nice to have a RANDBETWEENARRAY(101, 133, 6, 0), where the 3rd parameter is the size of the array, and the 4th parameter is 0=no repeats & 1=repeats allowed.

 

Another option on the future-Excel-wishlist would be to have a single-value random number generator where we tell it to exclude the values in certain other cells:

 

A6 = RANDBETWEENEXCLUDE(101, 133, A$1:A5), where the 3rd parameter tells the function what values (in what cells) to exclude.

 

This requires an ordered (non-recursive) sequence of cell evaluation, which may make that untenable.

 
•  
 
•