excelrandomexcel-formula

How to sample a range of Excel values (i.e. one column) to pick 100 random values

I have a column of 1,800 distinct values in Excel.

I want to randomly sample 100 of them.

What's the Excel way to do it?

I looked here and use the approach described there:

=INDEX($A$2:$A$10,RANDBETWEEN(1,COUNTA($A$2:$A$10)),1)

But I wonder if there's any other way...

Solution

  • Assume the source range is "A1:A1800", the target place is "B1:B100":

    [B1]=TAKE(SORTBY(A1:A1800,RANDARRAY(1800)),100)

    This method takes exactly 100 values with guarantee.