At 16:37 22/08/2016 +0200, Wiebe van der Worp wrote:

`There is a group of ~100 people (n). In 5 time slots they visit 5
``rooms in groups of ~20 people (n/5). The composition of the groups
``should be as different as possible for each time slot. One person
``may not visit a room more than once.
`

`I'm guessing your "about" 20 is because of the "about 100"; do you
``actually need all groups to be exactly n/5? I also get the general
``idea of "as different as possible", of course - but I think you'd
``need to define this more precisely if you need to balance this
``requirement against other constraints.
`

`Suppose Column A contains id's, say 1..100. Col. B contains first
``time slot room numbers: =INT(RAND()*5+1).
`

`That will put everyone into a group, but by no means produce equal
``n/5-sized groups.
`

`C, D and E contain room numbers for time slot 2, 3 and 4. F contains
``time slot 5, for row 1: =15-SUM(B1:E1). Does anyone have a
``suggestion for C1, D1 and E1?
`

`I think this is deceptive: the last grouping is easy only if you have
``already come up with a reliable algorithm for the intervening columns!
`

`What you need here is a shuffling process. You can see it as
``shuffling your hundred people and then dividing them into five
``groups, but that makes ensuring that each person visits all five
``rooms problematic. And I'm guessing that this is your principal
``criterion. Better, then, to see the problem as shuffling the rooms
``that each person is to visit.
`
Here's an idea:

`o In G1, enter =RAND() and fill this across and down to populate
``G1:K100 with random numbers. (You can hide this range or put it away
``on another sheet if you prefer.)
`
o In B1, enter =RANK(G1;$G1:$K1) and fill this across and down to fill B1:F100.

`This will ensure that each person visits each room exactly once, but
``the groups sizes will vary from exactly n/5. You could use COUNTIF()
``to list the sizes of the twenty-five groups and determine the range
``of sizes using MIN() and MAX(). Then you could use Recalculate Hard
``(Ctrl+Shift+F9) repeatedly to regenerate the random numbers until you
``were happy with the group sizes.
`

`This problem may be of the sort that would be much easier to solve
``through programming that via a spreadsheet.
`
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

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