Is it possible to do a random sample of non-numeric data in Excel?

[Guest]

Is it possible to do a random sample of non-numeric data in Excel? If so,
what do I do?

 

[Niek Otten]

What kind of data do you require? Example?

 

[Guest]

yes:


Lets say you have a very long list of animals in column A:
cat
dog
fish
....

and you want to select 10 items in this list at random. Then in B1 enter
=rand() and copy down. Sort both columns by column B. This will shuffle the
items in column A. Just pick the first ten.

To get a different sample, just touch F9, re-sort and pick the top ten again.

 

[RagDyeR]

Say your datalist was in A1 to A25.

In an out-of-the-way location, say Z1,
enter this formula:
=RAND()
And drag down to copy to Z25.

Then enter this formula anywhere you wish, and copy down for as many random,
without replacement samples you wish:

=INDEX($A$1:$A$25,RANK(Z1,$Z$1:$Z$25))

The selection will change with every hit og the <F9> (calculate) key.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Is it possible to do a random sample of non-numeric data in Excel? If so,
what do I do?

 

[Guest]

Hi,

If your non-numeric data start at A2 (and extend down to say A101), create a
range of random numbers in B2:B101 (for this, in B2 enter =RAND() and
autofill the formula down to B101). In C2 enter the formula,

=INDIRECT("A"&MATCH(LARGE($B$2:$B$101,ROW(A1)),$B$2:$B$101,0)+1)

Modify the range "$B$2:$B$101" to suit to your data; however don't change
the "ROW(A1)" in the formula.

The formula would return in C2 a random sample from column A. Drag the
formula in C2 to as many random samples as you want (e.g., if you want 10
samples, autofill the formula down to C11).

If your data start not at row 2 but in some other row, you have to modify
the formula appropriately. The "1" at the very end of the formula should be
changed to one less than the starting row number (e.g., if your data start at
A6, the number should be changed as "5").

Regards,
B. R. Ramachandran