MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### random select a percentage from a list

• Follow

``` 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?
```
 0

```=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.
--
Allllen

"Rocetman" wrote:

>  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?
```
 0

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

"Rocetman" <Rocetman@discussions.microsoft.com>
wrote in message
news:3BEE9A91-8C92-4714-B761-DEEBED9F913A@microsoft.com...
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?
```
 0

```"Rocetman" <Rocetman@discussions.microsoft.com> 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.

```
 0

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

> "Rocetman" <Rocetman@discussions.microsoft.com> 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).

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

```
 0

```"James Silverton" <not.jim.silverton@verizon.net> wrote:
> Joe  wrote  on Tue, 6 Apr 2010 09:50:00 -0700:
>> 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.

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

"James Silverton" <not.jim.silverton@verizon.net> wrote in message
news:%23iJN67a1KHA.4560@TK2MSFTNGP02.phx.gbl...
> Joe  wrote  on Tue, 6 Apr 2010 09:50:00 -0700:
>
>> "Rocetman" <Rocetman@discussions.microsoft.com> 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).
>
> 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

```
 0

5 Replies
911 Views

Similiar Articles:

7/20/2012 1:18:31 PM