challenge...

users
#1

Hi, I've got a challenge where I want Calc to do something for me. Here I go:

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.

Suppose Column A contains id's, say 1..100

Col. B contains first time slot room numbers: =INT(RAND()*5+1)

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?

#2

Hi Wiebe,

I wouldn't use random numbers, but work with primes below 20. So,
always starting with 1, and then increasing with the prime number
until you reach 100.

You have the following prime numbers : 19,17,13,11,7

Time slot 1 :
group A : 1, 20, 39, 58, 77, 96, (1)15, 34, 53, 72,...
group B : starting with the lowest available number.
group C : starting with the lowest available number.
...

Time slot 2 :
group A : 1, 18, 35, 52, 69, 86, (10)3, 20, 37, 54, 71, 88, (10)5, ...
group B :starting with the lowest available number.
group C : starting with the lowest available number.
...

Time slot 3 : same, but with an increment of 13
Time slot 4 : same, but with an increment of 11
Time slot 5 : same, but with an increment of 7

It might be even more unique when you use even higher primes, but I
guess this methodogy will be fair enough.

It's up to you to write the formulas :slight_smile:

Good luck!

Met vriendelijke groeten, Salutations distinguées, Kind Regards,

DRIES FEYS
CORPORATE SERVICES • Specialist Software Developer

TVH GROUP NV
Brabantstraat 15 • BE-8790 WAREGEM
T +32 56 43 42 11 • F +32 56 43 44 88 • www.tvh.com
Watch our company movies on www.tvh.tv

Business Innovation
through
Information Technology
bi2t.tvh.com

#3

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

#4

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 wouldn't use random numbers, but work with primes below 20. So,
> always starting with 1, and then increasing with the prime number
> until you reach 100.

> It might be even more unique when you use even higher primes, but I
> guess this methodogy will be fair enough.

Dries, thank you very much. Unfortunately I don't understand your intentions, other than that you created a list of unique numbers, right?

I am still curious if there is a smart approach. The question looks like what is described here: https://productforums.google.com/forum/#!topic/docs/EMlF95NfI5o

For now I did some copy paste work with calculation of last values in order to get 120 unique combinations, should work.

1 2 3 4 5
1 2 3 5 4
1 2 4 3 5
1 2 4 5 3

...

5 4 2 1 3
5 4 2 3 1
5 4 3 1 2
5 4 3 2 1

#5

This was my initial thought, as well. A problem-solving tool like Lingo (which I used in my Operations Research class 10 years ago and still find useful) would be perfectly suited to something like this.

Dave Liesse

#6

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.

Here's an idea:

...

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.

Your solution is way more elegant than mine and I will put these control measures at work, thanks. On the other hand, the deviation of number of people per room is quite unexpected, forcing a lot of Ctrl-Shift-F9 strokes.

This problem may be of the sort that would be much easier to solve
through programming that via a spreadsheet.

As Dave Liesse confirmed, driving in nails with the flat side of an axe. But it works for one time usage :wink:

Thanks to everyone for helping.