random selection of cells in excel

[Novice]

Dear All,

I need to randomly select 1 out of n subjects (n=2,...5 etc). I know
the typical approach is to assign random numbers to all n subjects,
and pick the one corresponding to the first random number in the
string of numbers (i.e. I get 3, 4, 1, 2 as a string of random
numbers, this means that I need to select subject/cell number 3).

Can somebody please advise if the following is acceptable: assign
random numbers and then select the one that corresponds to the maximum
(or minimum). I'm doing this in excel, so the random numbers generated
have values between 0 and 1 (rand() function).

The reason I want to use this approach is that I need to randomly
select for a postal survey purposes one household member from each
household. There may be up to 5 members in each household, and there
are total of 1000 households. If selection of max(min) random number
appoach is used it is easy to make escel to work out all necessary
selections. Otherwise i don't see any other choice but do it manually!


Thanks,

Novice

 

[Peo Sjoblom]

There are many ways to do this, you could let excel select a number

=INT(RAND()*(5-1)+1)

the above will return random integers between 1 and 5
or you can assign random numbers to all, then sort and select the first n
numbers

I would probably do as follows, assume I want to get one randomly selected
out of 10,
I would give all 10 a number from 1 to 10 and then I would use this formula

=INT(RAND()*(10-1)+1)

 

[Novice]

Dear Peo Sjoblom,

Many thanks for your suggestions.

it was good to find out how to use rand() function to generate numbers
in the specified range. Although I noticed that INT(RAND()*(5-1)+1)
function would generate numbers between 1 and 4 (inclusive), so I used
INT(RAND()*5+1) to generate 1 to 5


using your suggestion I eventually got to the result I wanted on a
test sample. So I'm hoping I can make it work for the whole sample,
however it does look much more tedious compared to what (I think) you
called "sort and select" approach.


In my example if I have a household of say 4 people, I generated 4
random numbers using simple rand() function, i.e. 1 was assigned
0.591350778, 2 - 0.142655972, 3 - 0.867318127, 4 - 0.235631605. Then
these were sorted by random number values descending and I picked the
top one (the one with the largest value, i.e. 3 - 0.867318127). In the
literature, however, I couldn't find any reference to acceptability of
such "sort and pick the largest" approach to random selection.
Intuitively, I feel this shouldn't be a problem, but I would like to
have some confirmation from someone with expertise on the subject. To
your knowledge, is this an appropriate procedure? Would you know any
references?


Thanks for your time and any help you can give.

Regards

Novice