RAND() Function

  • Thread starter Thread starter Debbie
  • Start date Start date
D

Debbie

I am trying to use RAND()to randomly select people each
month. My problem is that some people come up 2-3 times
and some never come up. I used a twelve month test
period. Group 1 is row 1-9, group 2 10-17, group 3 18-27,
group 4 28-36. Formula on row 1 column 2:=TRUNC(RAND()*9)
+1, row 10 column 2:=TRUNC(RAND()*8)+10, row 18 column
2:=TRUNC(RAND()*10)+18 and row 28 column 2:=TRUNC(RAND()*9)
+28. Total 36 people. The formula should select a
different person out of each group each month, for a total
of four people each month. With a twelve month test
period each person should be selected at least once. I
need to know what is wrong with my formula. Where can I
find a sample to follow?
 
I am trying to use RAND()to randomly select people each
month. My problem is that some people come up 2-3 times
and some never come up. I used a twelve month test
period. Group 1 is row 1-9, group 2 10-17, group 3 18-27,
group 4 28-36. Formula on row 1 column 2:=TRUNC(RAND()*9)
+1, row 10 column 2:=TRUNC(RAND()*8)+10, row 18 column
2:=TRUNC(RAND()*10)+18 and row 28 column 2:=TRUNC(RAND()*9)
+28. Total 36 people. The formula should select a
different person out of each group each month, for a total
of four people each month. With a twelve month test
period each person should be selected at least once. I
need to know what is wrong with my formula. Where can I
find a sample to follow?
Click to expand...

RAND doesn't work like you believe it does. When you truncate RAND()*N to an
integer, there are likely to be duplicates. You need to create a range of 36
cells each containing =RAND(). If this range were X1:X36 and your 36 people were
listed in A1:A36, then entering the following formula in B1

=INDEX($A$1:$A$36,MATCH(SMALL($X$1:$X$36,ROW()),$X$1:$X$36,0))

and filling B1 into B2:B36 will give you all 36 people in random order in
B1:B36. The same thing could be accomplished by putting the =RAND() formulas
into B1:B36 and sorting A1:B36 on column B (containing the RAND formulas) in
either ascending or descending order. In that case, A1:A36 would then be in
random order.
 
Debbie,

Your assertion that "With a twelve month test
period each person should be selected at least once" is not true. The
probability of not being selected in each period is 8/9 and the probability
of not being selected in all 12 is (8/9)^12 or 24%. Thus, the result of
your test is certainly not an indication that your formula is not working.
Unless of course you do wish that every person be selected at least once.
(In which case I'm drawing a complete blank at the moment on how to go about
that)

Also, I believe that RANDBETWEEN(lower,upper) may be easier to work with if
you want to assign random integers within a specified interval.

PC
 
-----Original Message-----

Debbie,

Your assertion that "With a twelve month test
period each person should be selected at least once" is not true. The
probability of not being selected in each period is 8/9 and the probability
of not being selected in all 12 is (8/9)^12 or 24%. Thus, the result of
your test is certainly not an indication that your formula is not working.
Unless of course you do wish that every person be selected at least once.
(In which case I'm drawing a complete blank at the moment on how to go about
that)

Also, I believe that RANDBETWEEN(lower,upper) may be easier to work with if
you want to assign random integers within a specified interval.

PC





.
Thanks Paul and Harlan for replying so quickly. In
Click to expand...
response, I would like to give a little more information
on what my assignment is. I need to schedule one person
out of each group of people for testing each month. The
same person(s) should not come up for testing each month.
Testing has to be done randomly but everyone should come
up for testing at least once during the year. Group one
has 9 people, group 2 8 people, group 3 10 people and
group 4 9 people for a total of 36. Running the RAND()
function would leave some people out completely while
others would come up for testing all the time. I did take
your suggestion and try RANDBETWEEN(bottom,top) and it
does the same thing. Is there software we could buy that
has a better program than Excel?
 
...
...
Thanks Paul and Harlan for replying so quickly. In
response, I would like to give a little more information
on what my assignment is. I need to schedule one person
out of each group of people for testing each month. The
same person(s) should not come up for testing each month.
Testing has to be done randomly but everyone should come
up for testing at least once during the year. Group one
has 9 people, group 2 8 people, group 3 10 people and
group 4 9 people for a total of 36. Running the RAND()
function would leave some people out completely while
others would come up for testing all the time. I did take
your suggestion and try RANDBETWEEN(bottom,top) and it
does the same thing. Is there software we could buy that
has a better program than Excel?
Click to expand...

It can be done, but I believe it requires a bit of setup. I'll assume
individual's IDs in A1:A36 of the form {AA;AB;AC;AD;AE;AF;AG;AH;AI} in A1:A9,
{BA;BB;BC;BD;BE;BF;BG;BH;BI} in A10:A18, etc. I'll assume every cell in B41:M44
contains the formula =RAND(). Then I'll enter the following *array* formulas.


B1:B9 -
=IF(INT(9*B$41)=ROW()-1,$A1:$A9,"")

B10:B18 -
=IF(INT(9*B$42)=ROW()-10,$A10:$A18,"")

B19:B27 -
=IF(INT(9*B$43)=ROW()-19,$A19:$A27,"")

B28:B36 -
=IF(INT(9*B$44)=ROW()-28,$A28:$A36,"")


C1:C9 -
=IF(SMALL((1-COUNTIF($A1:$A9,B1:B9))*ROW(),
2+INT(8*C$41))=ROW(),$A1:$A9,"")

C10:C18 -
=IF(SMALL((1-COUNTIF($A10:$A18,B10:B18))*ROW(),
2+INT(8*C$42))=ROW(),$A10:$A18,"")

C19:C27 -
=IF(SMALL((1-COUNTIF($A19:$A27,B19:B27))*ROW(),
2+INT(8*C$43))=ROW(),$A19:$A27,"")

C28:C36 -
=IF(SMALL((1-COUNTIF($A28:$A36,B28:B36))*ROW(),
2+INT(8*C$44))=ROW(),$A28:$A36,"")


D1:D9 -
=IF(SMALL((1-MMULT(COUNTIF($A1:$A9,B1:C9),{1;1}))*ROW(),
3+INT(7*D$41))=ROW(),$A1:$A9,"")

D10:D18 -
=IF(SMALL((1-MMULT(COUNTIF($A10:$A18,B10:C18),{1;1}))*ROW(),
3+INT(7*D$42))=ROW(),$A10:$A18,"")

D19:D27 -
=IF(SMALL((1-MMULT(COUNTIF($A19:$A27,B19:C27),{1;1}))*ROW(),
3+INT(7*D$43))=ROW(),$A19:$A27,"")

D28:D36 -
=IF(SMALL((1-MMULT(COUNTIF($A28:$A36,B28:C36),{1;1}))*ROW(),
3+INT(7*D$44))=ROW(),$A28:$A36,"")


Copy D1:D36 and paste into E1:M36. Each column of B1:M36 should now contain the
ID of the person from each group to be tested in the given month. Note: the
column D and rightwards formulas ensure that no one is tested more than once in
any 3 month period. If you only want to prohibit testing any individual more
than once in every *2* month period, ignore the column D formulas, copy C1:C36
and paste into D1:M36.
 
[This followup was posted to microsoft.public.excel.worksheet.functions
with an email copy to Debbie.
Please use the newsgroup for further discussion.]

I don't know if Harlan's worksheet solution implements the algorithm
below (or something equivalent). But, here's how I would do it.

The two assumptions are (1) only 1 person can be tested each month, and
(2) each person must be tested at least once in a (rolling) 12 month
period.

First, we need to test and see if there is a "must test from" list. If
this list is empty, then we pick someone totally at random from the
group.

What is this "must test from" list and how do we create it? The list
comes into existence from the two assumptions.

Look back over the last 11 months. If there is one person who has not
been tested, that person *has* to be the sole member of the "must test
from" list. No ifs or buts.

If the list is empty, look back 10 months. If there are 2 people who
haven't been tested, both of them will be members of the "must test
from" list. And, one of them *must* be selected for testing. If that
doesn't happen, the next month there will be two people not tested for
11 months and at least one of the assumptions will have to be violated.

If the list is still empty, look back 9 months. If there are 3 people
who haven't been tested, they belong in the list and one of them *must*
be selected.

Step through the process until you are down to a test group size
greater than your actual group size.

If the "must test from" list still is empty, just pick any person at
random.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top