Re: [libreoffice-users] random # in calc

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!


On Thu, Nov 14, 2013 at 4:05 AM, Brian Barker <b.m.barker@btinternet.com>wrote:

> 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


-- 
Helen Etters
using Linux, suse12.3

-- 
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