Random Values

[ziggi456]

Hi
I have a list of people (approx 200) and need daily to pick 20% of these
people on a random search

 

[Sheeloo]

Assign numbers (say in Column A) from 1 to 200 to the people on your list
Enter the following in any column
=ROUNDUP(A1*200,0) and copy down to A20

You can use the numbers generated to pick up 20 names at random
You can enter =VLOOKUP(B1,A:A,1,False) in C1 and copy to C20 to get names in
Column C...

 

[Sheeloo]

Sorry VLOOKUP should be entered in D1 and copied to D20
=VLookup (C1,A:B,2,False)

assuming you have entered the RAND() formula in C1 and your Serial Numbers
are in Col A and Names in Col B...

 

[Sheeloo]

It is late and I should go to sleep, hope I get it right this time;

1. Insert Column A and enter numbers from 1 to 200
2. Have names in Column B
3. In C1 enter
=ROUNDUP(RAND()*200,0)
and copy down to C20
4. in D1 enter
=VLookup (C1,A:B,2,False)
and copy down to D20

Press F9 whenever you need a new set of 20 random names...

 

[Max]

I have a list of people (approx 200) and need daily to pick 20% of these
people on a random search

Another play to tinker with ..
Assume names listed in A1:A200
Put in B1: =RAND()
Copy down to B200

Put in C1: =INDEX(A:A,RANK(B1,$B$1:$B$200))
Copy C1 down to say, C40, to return 40 random names from the list in col A
(your 20% of 200). Or, copy down all the way to C200 to return a full random
scramble of the entire lot. Press F9 to regenerate.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800 Files:359 Subscribers:55
xdemechanik
---

 

[Shane Devenshire]

Hi,

An alternate method is to use the Analysis ToolPak's Random Sampling tool,
provided you have some numerical data, such as an employee number associate
with each one.

1. Attach the tool in 2003 or earlier by choosing Tools, Add-Ins, Analysis
ToolPak
2. Choose Tools, Data Analysis, Sampling.
3. Fill in the dialog box as desired using the numerical data as the Input
range.

If the data is non-numeric then suppose the names are in the range B1:B1000,
then
1. In cell A1 enter the formula =RAND() and copy it down as far as
necessary.
2. In a blank cell enter the formula:

=IF(ROW(A1)<=ROUND(0.2*COUNTA($B$1:$B$1000),0),VLOOKUP(LARGE($A$1:$A$1000,ROW(A1)),$A$1:$B1000,2,0),"")

Copy this formula down until it does not display any results - 20% of 1000
would be 200 row.

or you could hardcode the 200 into the formula:

=IF(ROW(A1)<=200,VLOOKUP(LARGE($A$1:$A$1000,ROW(A1)),$A$1:$B$1000,2,0),"")

Cheers,
Shane Devenshire
Microsoft Excel MVP

 

[Gaurav]

Would you want a random sample generator? if yes...post your email address.

 

[Max]

To easily reach out to all interested readers out here,
why not upload your file using a free filehost, eg:
http://www.freefilehosting.net/

Then just post the direct link to it here

(I practice the above)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800 Files:359 Subscribers:56
xdemechanik
---