Re: [libreoffice-users] random # in calc

At 10:17 11/11/2013 -0500, Helen Etters wrote:
> 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


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted