Generating unique random numbers in Excel using fo

问题:

Closed. This question is off-topic. It is not currently accepting answers.

---

Want to improve this question? Update the question so it's on-topic for Stack Overflow.

Closed 7 years ago.

I have to generate five distinct random numbers from 0 to 50 using only formulas and not VBA. How should I do it?

回答1:

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

回答2:

You may want to round the following depending on your use.

=RAND() * 50