Random Selection

[petevang]

Can excel be used to randomly select numbers within a group. Ex I need to randomly select 14 accounts aout of a list of 150. How can I do this?

[Rich_z]

You don't say if the accounts are consecutively numbered or not.

If they are consecutively numbered you can use:

Please Login or Register  to view this content.

If they are not consecutively numbered then place the accounts into an array numbered 1-150 and use the formula above to generate a pointer into the array (likewise if the accounts are non-numeric)

RND is a psuedo Random number generator. This means that it's results are reproduceable (if you restart Excel...).

To reproduce a given set of 'Random' numbers you use the 'Randomize' statement.

To give the numbers an almost totally random nature, use the statement:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Gives the following results over 3 runs:

94
78
114
67
85
52
79
95
124
58
36
40
125
132

46
47
35
46
81
100
33
70
143
150
120
37
51
140

122
21
14
93
45
24
122
52
101
92
148
71
98
133


If you want to make sure a number is not used twice then you will have to build an array of the numbers chosen (either in a string or using Redim)

Regards

Rich

[Tom Ogilvy]

put the list of 150 accounts in column A starting in row1

in column B put in

=rand()

and drag down.

Then sort A1:B150 on column B.

Take the first 15 accounts

--
Regards,
Tom Ogilvy

"petevang" <[email protected]> wrote in
message news:[email protected]...
>
> Can excel be used to randomly select numbers within a group. Ex I need
> to randomly select 14 accounts aout of a list of 150. How can I do
> this?
>
>
> --
> petevang
> ------------------------------------------------------------------------
> petevang's Profile:
http://www.excelforum.com/member.php...o&userid=25034
> View this thread: http://www.excelforum.com/showthread...hreadid=386134
>