random # in calc

users
#1

A LO calc question. It's a bit trivial, but I'm sure it can be done.
Names of about 20 people. Draw names for Christmas gifts.
We can wait until we're all together in a couple of weeks, and that
will work, but can we do this by running a random number generator
in calc? Type 20 names into column one, then the same names again
in column 2, then run a random number generator to match them up?
I did something similar to this about a year ago (for a church dinner)
but for the life of me I just can't remember how I did it, and the
instructions
I get by googling don't work. (When I follow those instructions, the
function returns #NAME? in the cell.)
Thanks for any pointers,

#2

Hi.
If the selection is random, you could possibly select the same person twice (or more) to match another person.
Just off the top of my head, you could have 4 columns. Names1 and next to it random1 "=RAND()" copied down.
Repeat the 2 columns for the second list of names. Now highlight the first pair (Names1 and next to it random1) and on the menu Data>Sort and sort by the random number column. Do the same with the second pair.
Someone will have a far more elegant method, but at least you can start shopping.
Steve

#3

A LO calc question. It's a bit trivial, but I'm sure it can be done. Names of about 20 people. Draw names for Christmas gifts.

Have you wondered why you have got little in the way of replies? Er, perhaps "Draw names for Christmas gifts" is not a clear definition of a problem! You have three presents to give away and you just want pick three names as gold, silver, and bronze winners - right? Oh, - or you want to divide the twenty into two groups, ten givers and ten receivers - right? No?

We can wait until we're all together in a couple of weeks, and that will work, ...

I'm finding it difficult to understand how the subjects' physical presence will simplify your task. Are you going to give them random numbers on pieces of paper and ask them to do a dance to arrange themselves in order by value? Is that called Musical Numbers? That's a sort of Christmas party I'm not familiar with.

... but can we do this by running a random number generator in calc?

Very probably.

Type 20 names into column one, then the same names again in column 2, then run a random number generator to match them up?

Ah, so you are somehow matching twenty with the same twenty. What does that hint to us about your actual needs? Hmm, let's see now. Perhaps you want each of the twenty to give a gift to another of the twenty - and to randomise the results. Is that it?

Well, if you randomise the order of one column, as you are perhaps suggesting, it is possible (quite likely?) that someone will be selected to give their gift to themselves; is that OK? Perhaps not. Let's try to avoid that and get everyone giving to someone else. But now we may have another problem. In general, each person will give to and receive from different people, but it may happen that the same people are chosen both ways - so that one pair of individuals simply exchange presents both ways. Does that matter? Do you see your problem needs more careful specification?

I did something similar to this about a year ago (for a church dinner) but for the life of me I just can't remember how I did it, and the instructions I get by googling don't work. (When I follow those instructions, the function returns #NAME? in the cell.)

It's not much help to know that unless you explain what the instructions were.

There ought to be a neat way to do this, but I don't immediately see one. Try this slightly messy one:

o Enter the names into column A. I'm assuming there are twenty.

o Copy these into column B. (Don't retype or you may create differences.)

o In C1, enter =RAND() and fill this down column C.

o In D1, enter =IF(A1=B1;"Themself!";IF(VLOOKUP(B1;A$1:B$20;2;0)=A1;"Each other!";"")) and fill this down column D.

o Select the data in columns B and C, i.e. the range B1 to C20.

o Go to Data | Sort... . For "Sort by", select "Column C". (It doesn't matter whether you choose Ascending or Descending.)

o Click OK.

o The names in column B have now been randomised, but you will probably find that column D shows either some people selected to give to themself or some selected to give to each other. If so, go to Data | Sort... | OK repeatedly until column D is blank and shows no problems. Each time you sort, the random numbers are recalculated and the order in column B changes. For twenty names, you may need a few sorts to clear errors but you should get there fairly quickly.

o Print columns A and B. (Set a print range for convenience.)

If you need too many sorts to achieve a result, lose some friends.

I trust this helps.

Brian Barker

#4

How very clever! Yes, this worked! I did get three "each other"
responses and I will study the code and try to figure out how it works.
Thank you so much!

#5

Hi :slight_smile:
Computers don't usually do "random" very well.

Prolly better to wait until everyone is together (or at least enough
of you that no-one is going to feel the results were "fixed") and then
draw names from a hat. Print the names on slips of paper and then
make a big show of pulling out a name at random. A twist is for the
1st winner to pick the 2nd name and then the 2nd winner picks the 3rd
slip. This twist further minimises accusations of it being "fixed".

You might be able to buy a 20 sided dice from a games shop such as
"Games and Puzzles" (if they still exist) or find one in a box of
"Dungeons and Dragons" or "Runequest" or some-such.
Regards from
Tom :slight_smile:

#6

Actually Brian's idea worked very well. But as I said in my Q, it's a
rather trivial need. Usually,
at Thanksgiving, we toss handwritten names on paper scraps into a real hat,
and everyone draws a
name. Some people have said they don't like to wait that late to do their
shopping, so the
virtual hat occurred to me. As I look at Brian's "IF" code, I understand
what the script is doing, so
I've learned something useful, and hope others on the list have as well.

#7

Computers don't usually do "random" very well.

Computers actually do (pseudo-)random extremely well.

Prolly better to wait until everyone is together (or at least enough of you that no-one is going to feel the results were "fixed") and then draw names from a hat.

Three problems:
o It doesn't work: some people will draw themselves.
o You need to do it all twice.
o It still doesn't work: some people will draw themselves and some will have drawn each other.

Print the names on slips of paper and then make a big show of pulling out a name at random. A twist is for the 1st winner to pick the 2nd name and then the 2nd winner picks the 3rd slip.

There are no "winners" here: everyone is both a winner and a loser.

Brian Barker