Random Numbers

[Guest]

How do I set up a program to randomly select a number from a list between
1-15. On one, I need to pick 1 number everyday for 5 days without picking
the same number during those 5 days.

On the other I need to pick 2 numbers between 1 and 35 daily for 5 days,
again without repeating the same number during the 5 days.

Thank you for your help.

 

[Guest]

With numbers 1 to 15 in column A, put =RAND() in B1 and copy down to B15.
Sort columns on Column B and select first 5 (one for each day) from A.

Do similar exercise for 35 numbers, select top 10, allocating 2 per day.

HTH

 

[James Silverton]

loida wrote on Tue, 24 Jul 2007 14:08:08 -0700:

l> On the other I need to pick 2 numbers between 1 and 35 daily
l> for 5 days, again without repeating the same number during
l> the 5 days.

Unless you allow the possibility of a number occuring more than
once, your numbers are not truly random (RANDBETWEEN() will
work). Two columns, one of numbers and the other of RAND() will
do what you want, sorting on the second column.


James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.verizon.not

 

[Ragdyer]

Why not pick those 5 different numbers all at the same time, once a week.

Enter the Rand function in an out-of-the-way location, say Z1:
=Rand()
And copy down to Z35.

Say in A1 to A5, you enter Mon. to Fri.

Then in B1, enter:
=INDEX(ROW(A$1:A$15),RANK(Z1,Z$1:Z$15))
And copy down to B5.

This gives you your random 5 out of 15 without replacement.

To get your 2 per day out of 35, enter this formula in say D1:

=INDEX(ROW($A$1:$A$35),RANK(INDEX($Z$1:$Z$35,(2*ROWS($1:1))-2+COLUMNS($A:A)),$Z$1:$Z$35))

And copy across to E1, then copy that 2 cell selection down to E5.

Youi'll now get your 2 per day random pick without replacement.

Each time you hit <F9>, you'll get a new set of random numbers.

 

[Guest]

Thank you, I like this one, it works.

Why not pick those 5 different numbers all at the same time, once a week.

Enter the Rand function in an out-of-the-way location, say Z1:
=Rand()
And copy down to Z35.

Say in A1 to A5, you enter Mon. to Fri.

Then in B1, enter:
=INDEX(ROW(A$1:A$15),RANK(Z1,Z$1:Z$15))
And copy down to B5.

This gives you your random 5 out of 15 without replacement.

To get your 2 per day out of 35, enter this formula in say D1:

=INDEX(ROW($A$1:$A$35),RANK(INDEX($Z$1:$Z$35,(2*ROWS($1:1))-2+COLUMNS($A:A)),$Z$1:$Z$35))

And copy across to E1, then copy that 2 cell selection down to E5.

Youi'll now get your 2 per day random pick without replacement.

Each time you hit <F9>, you'll get a new set of random numbers.

 

[Ragdyer]

You're welcome, and thanks for the feed-back.