Generating unique random numbers in Excel using formula without VBA

excel, excel-formula, random

Solution

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

Problem

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

Original source