2013 Archives by date, by thread · List index

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

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

### Context

Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.