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