Random selection

[Lurking.]

Hello everyone.

30 years ago I used to use the @RAND function in Lotus Symphony for selecting names for indoor bowls.

Mr Google has not been much help, except to point me to your website.

Col.A Col. B Col C
List of players Game 1 Game 2
Lester
Betty
Zack
so on down
to
25
players

So we need random selections for for games 1 and 2.

I hope this is the right forum for the @rand or =rand function.

Thanks for your replies.

Lurking.

 

[tkhunny]

MS Excel has =RAND() and =RANDBETWEEN(low,high).

Is there a question, here?

 

[Lurking.]

Hi tkhunny, thanks for your reply.

So we need random selections for for games 1 and 2.

Sorry full stop should have been a "?"

Threw that formula low,high into XL2000 and it comes back with "#NAME?".

=RANDBETWEEN(B2:B30)

Regards,

Lurking.

 

[tkhunny]

Right. Find your "Add Ins" and activate "Analysis Tool Pack" and "Analysis Tool Pack - VBA" and try again.

 

[Lurking.]

tkhunny, thanks for that.

That worked, but I think there might be a bit more to it.

Have attached a thumb nail of problem, I need to give each player a random number, for each game, unless the names can be randomly mixed for the two games ?.

As I said originally, if only it was 30 years ago, lol.

Regards,

Lurking.

 

[tkhunny]

I see. You need selection without replacement. You may have to do some sorting. It would not be TOO hard to write a little VBasic routine.

You could try this...http://www.youtube.com/watch?v=SoK9kq-0uXg

 

[Lurking.]

Hi tkhunny, thanks for that, we may have to use it, but it does seem a long winded way of doing it.

There used to be a very and probably still is simple random selection for Lotto, 1 to 7 here in up-side-down land.

If you wanted a ticket with say 6 rows, you merely entered the @rand formula again for a new row.

Used to do quite a bit of formula spreadsheets for the manufacturing company I worked for, b u g g e r that was 30 years ago when I was aged 50, but one does appear to be on a grassy slope, lol.

Thanks again for your efforts.

Lurking.

 

[Denis]

Have attached a thumb nail of problem, I need to give each player a random number, for each game...

12 players, 2 games: so in this case, you're trying to select, at random, numbers 1 to 24; right?

As TKHunny says, easy to write a Basic program to do this:
will really work same as shuffling a deck of 52 cards...

 

[Lurking.]

Hi Denis, thanks for your reply.

Would appreciate any help on the Basic program for 30 players.

tkhunny there was another video on the site you gave which would also be of help.

Thanks in advance.

Lurking.

Ps. we have had 2 earthquakes here in Christchurch, New Zealand and the 2nd one in February knocked a wardrobe over and out came a box of carton of 5.25 floppies and I bet if I had a pc that could take those disks I would find the solution, lol.

lurks.

 

[JeffM]

I am still not sure I understand the problem.

Are the "random numbers" to be unduplicated integers from 1 to 30 in a random order?

Or do you just want a random number between 0 and 99 for each person?

Or do you want an unduplicated random number between 0 and 99 for each person?

Sorry to be stupid

 

[Lurking.]

JeffM, don't feel like that, it's moir who is the stupid one, they say memory declines after 40, only wish I could find where mine went to.

One could just do a manual sort, or even just take a deck of say 30 playing cards (but they are restricted to 13 cards).

There used to be a very simple little Lotus 123 formula @rand() for picking Lotto numbers, suppose we could always ask the Lotto people, lol when we ask for a "lucky dip".

In the attached file I have manually started a sort on the first game, so wouldn't it be better to have the computer do it and be much quicker toooo.

Thanks again,

Lurking.

 

[Lurking.]

Sorry, the attach. did not append.

Lurks.

 

[JeffM]

Lurking

I am unlikely to be a prompt responder: my wife fell on Saturday while we we were on a remote island in the Galapagos and broke her arm badly (or at least badly enough to be beyond the scope of what a clinic in the Galapagos is eager to cope with). I spent most of the two previous days getting her home with her arm in a splint. I feel like a mule from how much I carted around through airports, etc. Today was spent moving her from doctor's office to medical labs in preparation for surgery on Friday. So I have more pressing obligations than pondering a math problem.

Nor do I guarantee that I can solve your problem in a way simpler than coding an algorithm in Basic or Python. But I shall work on it when I can.

Unfortunately I do not know anything about Lotto. I presume it is a game that I am not familiar with.

Based on your example, I am GUESSING that, for n players, an integer ranging from 1 through n with no duplicates must be assigned randomly to each player. Is that correct? If that is the problem, the basic programming solution is to loop n times using a random number generator, apply modulo n arithmetic to the first two digits of the random number, and exclude previously selected values. That is, there is an outer loop of n, but an inner loop of k < n. There may or may not be a spreadsheet solution that is easier to implement, but I do not want to think about it until I am sure of what is required.

You are way deeper into what you need than I am, and I need to start at the beginning. So please let's take this one step at a time.

 

[Lurking.]

Hi JeffM, very sorry to hear about your wife, hope she makes a speedy recovery.

Checking out Darwin's theory ?.

For a temporary solution I will just use the =rand()*40 and format column to single integer.

Get your wife better and don't worry too much about this problem.

Kind regards,

Lurking.

 

[JeffM]

Get your wife better and don't worry too much about this problem.

I am just the chauffeur and dogsbody so I have time. Just not sure when I have time.

Checking out Darwin's theory ?.

More like observing it in action.

For a temporary solution I will just use the =rand()*40 and format column to single integer.

If the number of players = n and n = 40, the formula above will not generate solutions that are randomly distributed between 1 and 40 and is very likely to generate duplicate solutions.

Assume n is in in A1.

Assume the number to be assigned to the first party is in cell B2. The formula RANDBETWEEN(1,$A$1) in B2 will give you an integer that is randomly distributed from 1 through n.

When you copy down from B2, you will always get an integer in the presumably desired range, but, with high probability, there will be a few duplicates in the B column. I will think more about how to prevent duplicates via spreadsheet logic, but there may be no general solution within the constraints of that algorithm.

 

[Denis]

It is a VERY SIMPLE program when coded, but not so simple to "explain"!
See if you can kinda "follow this":

Say you want to distribute 5 numbers at random;
let's make the numbers 2,3,5,7,8.
Use array A (size 5) to record the numbers as they are randomly chosen,
and use array B (size 5) in which the 5 actual numbers are entered:
A [1]0 [2]0 [3]0 [4]0 [5]0
B [1]2 [2]3 [3]5 [4]7 [5]8

Choose randomly a number from 1 to 5; assume 3 :
enter the contents of B[3] in A[1];
move B(5) to B[3] ; so we have:
A [1]5 [2]0 [3]0 [4]0 [5]0
B [1]2 [2]3 [3]8 [4]7

Choose randomly a number from 1 to 4; assume 1 :
enter the contents of B[1] in A[2];
move B(4) to B[1] ; so we have:
A [1]5 [2]2 [3]0 [4]0 [5]0
B [1]7 [2]3 [3]8

Choose randomly a number from 1 to 3; assume 3 :
enter the contents of B[3] in A[3];
move B(3) to B[3] (so stays same) ; so we have:
A [1]5 [2]2 [3]8 [4]0 [5]0
B [1]7 [2]3

Choose randomly a number from 1 to 2; assume 1 :
enter the contents of B[1] in A[4];
move B(2) to B[1] ; so we have:
A [1]5 [2]2 [3]8 [4]7 [5]0
B [1]3

By default, A[5] = B[1] ; so we have
A [1]5 [2]2 [3]8 [4]7 [5]3 ..... and we're finished!

Don't hesitate if you have questions...
also if you'd like to see a Basic Language Coding (mine) that does this quite quickly;
(like 5000 numbers in less than a second!)

 

[Lurking.]

Thanks for all the replies guys.

A final explanation on how to get rid of the duplicated numbers would be appreciated, see the attached formula.

By the way, wife has the W7 and latest Excel goodies, but I still have a Win98se and XP SP3 machines with the "free" Excel 2000 which is good enough for this old timer, lol.

Kind regards,

Lurking.

 

[JeffM]

Lurking

I have had plenty of time sitting in waiting rooms to think about how Excel can generate a list of random integers within a range without any possibility of duplicates. I have not come up with a method. (That may say more about my lack of creativity than the limitations of Excel.)

As tkhunny and denis have both pointed out, the programming to generate such a list is quite simple in basic (and probably in vbasic, which I do not know.) Denis has already programmed it in a very elegant way (see his previous post). He seems willing to give you the source code if you have the appropriate compiler or can readily get it. Perhaps he knows how to send it to you as an executable.

 

[Lurking.]

Hi again JeffM, thanks for your input.

Yes I may have to go back to Basic, GWBasic is still on the the 98SE machine, but it's using a 16in LCD screen that got clobbered by the 6.3 earthquake in Feb. There is a 4inch square that is unreadable in the bottom r/h corner.

Have not waited for the EQC or Insurance to replace, so have put what I think the people will reimburse onto this XP machine, a 18.5 lcd wide screen.

Did learn Basic at Polytech which seems like a hundred years ago !.

Will copy this to Denis.

Kind regards,

Lurking.

 

[Lurking.]

Hi Denis, thanks for your reply. Here is what I replied to JeffM:

Hi again JeffM, thanks for your input.

Yes I may have to go back to Basic, GWBasic is still on the the 98SE machine, but it's using a 16in LCD screen that got clobbered by the 6.3 earthquake in Feb. There is a 4inch square that is unreadable in the bottom r/h corner.

Have not waited for the EQC or Insurance to replace, so have put what I think the people will reimburse onto this XP machine, a 18.5 lcd wide screen.

Did learn Basic at Polytech which seems like a hundred years ago !.

Will copy this to Denis.

Denis, thanks for your efforts and a file would be much appreciated.

Kind regards,

Lurking.