random select a percentage from a list

[Rocetman]

I am trying to randomly select a percentage from a list of 891 for
assessment interviews. Do I use Rand ()*.1 for 10 percent of the selection?

 

[Allllen]

=RAND()
gives you a number between 0 and 1

=RAND()*891
will give you a number between 0 and 891

You can round it to the nearest 1 with
=ROUND(RAND()*891,0)

If you only want to look at 10% of the 891, you can use
=ROUND(RAND()*891*0.1,0)
which will give you a number from 0 to 89.

It is not clear exactly what you mean but i think you'll get what you need
from what I have written.

 

[Jim Cone]

You can use the built-in Data Analysis / Sampling utility.
--
Jim Cone
Portland, Oregon USA



"Rocetman" <[email protected]>
wrote in message
I am trying to randomly select a percentage from a list of 891 for
assessment interviews. Do I use Rand ()*.1 for 10 percent of the selection?

 

[Joe User]

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.

 

[James Silverton]

Joe wrote on Tue, 6 Apr 2010 09:50:00 -0700:

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).

You might consider using randbetween(1,891), paste special value, and
then sort.

--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not

 

[Joe User]

Joe wrote on Tue, 6 Apr 2010 09:50:00 -0700:

You might consider using randbetween(1,891), paste special value,
and then sort.

It is unclear what part of my posting you are commenting on. I assume it is
that last statement, since you truncate the rest of my posting.

Of course, RANDBETWEEN(1,891) is not equivalent to
ROUND(C2*COUNTA(A2:A892),0). The latter results in a user-specified
percentage (in C2) of 891, whereas the former results in a random
"percentage" over which the use has no control.

However, it is a matter of interpretation of what exactly the OP means by
"randomly select a percentage from a list".

I interpret it to mean "randomly select from a percentage of a list".

You might be assuming it means "select from a random percentage of a list".
But that begs the question: is the selection itself random (i.e. "randomly
select from a random percentage of a list"), or does it mean "select the
first random percentage of a list"?

If the OP had meant as you might assume, the OP's phrasing would be poor
grammar (misplaced antecedent). But we've seen much worse, of course. In
fact, the entire sentence leaves room for wild interpretations, since the OP
does not say what the list is composed of. I assume it is a list of names.
It could be a list of percentages ;-).


----- original message -----