Prev: Using Tab key to jump to specific cell
Next: Trust Center - Trusted Locations are greyed out
From: Rocetman on 6 Apr 2010 10:35
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?
From: Allllen on 6 Apr 2010 11:58
=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?
From: Jim Cone on 6 Apr 2010 12:28
You can use the built-in Data Analysis / Sampling utility.
--
Jim Cone
Portland, Oregon USA



"Rocetman" <Rocetman(a)discussions.microsoft.com>
wrote in message
news:3BEE9A91-8C92-4714-B761-DEEBED9F913A(a)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?
From: Joe User on 6 Apr 2010 12:50
"Rocetman" <Rocetman(a)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.

From: James Silverton on 6 Apr 2010 13:31
Joe wrote on Tue, 6 Apr 2010 09:50:00 -0700:

> "Rocetman" <Rocetman(a)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

 |  Next  |  Last
Pages: 1 2
Prev: Using Tab key to jump to specific cell
Next: Trust Center - Trusted Locations are greyed out