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.
On Thu, Nov 14, 2013 at 10:49 AM, Tom Davies <[email protected]>wrote: > Hi :) > 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 :) > > > > > > > On 14 November 2013 09:05, Brian Barker <[email protected]> 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: [email protected] > > 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 > > > > -- > To unsubscribe e-mail to: [email protected] > 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: [email protected] 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
