random select a percentage from a list
"Rocetman" wrote:
I am trying to randomly select a percentage from a list of 891 for
assessment interviews.
If you want the percentage to be a "variable" -- a cell whose value you
provide, consider the following.
Suppose your data is in A2:A892. And suppose the desired percentage is in
C2, entered in the form 10% or 0.1. C2 can also be a random percentage,
which can be entered as =ROUND(RAND(),2) for example.
In some out-of-the-way range, say X2:X892, put the formula =RAND() into each
cell.
Then, if you want B2:B892 to contain the random selection of a percentage of
the list in A2:A892, enter the following formula into B2 and copy down
through B892:
=IF(ROW()-ROW($B$2)+1 $C$2*COUNTA($A$2:$A$892), "", INDEX($A$2:$A$892,
RANK(X2,$X$2:$X$892)))
Some important notes:
1. You might prefer ROUND($C$2*COUNTA($A$2:$A$892), 0).
2. Since RAND() changes every time you edit any cell in the workbook(!), you
might want to put the RAND formulas into some other cells, then
copy-and-paste-special-value into C2 and X2:X892. There are also other ways
of getting nonvolatile random values.
|