Excel Help

[John]

Hi folks,

Trying to make a spreadsheet here for my mum. Here are the rules to the session she's running for work next week:

1) There are 17 people attending the session in total.
2) There are 5 different workshops.
3) There must be 4 groups per workshop.

This means that she has to have 4 groups of 3, and 1 group of 5 (totalling 17).

Everyone has to mix up and work with different people for each session, but there are 4 managers you see. So these 4 managers will remain in the same group for each workshop, they'll just have 3 or 4 different people in their team for each workshop.

I'm trying to mix everything up in Excel, but I'm having difficulties. I have assigned ID numbers (01, 02, 03 through to 17) for everyone. Is it possible to use the random number generator to generate 4 groups of random people per session, also ensuring that there are zero, or very few crossovers? For example if Person 1 and Person 2 work together in the same group for workshop 1, they're not supposed to be in the same group again, but as I said, minimal amount of crossovers is acceptable.

This is my long winded version of saying that all I need to do is create forced randomness for 13 different people to ensure that they are only assigned the same number a maximum of two times but preferably once.

Any help would be much appreciated on this.

[Win2Win]

Isn't it easier using pen & paper? :)

[John]

Yeah, possibly. I was thinking that. But I like doing things properly on a computer. Do you think it would be easier just to jumble people up away from the screen?

[GlosRFC]

Allocating them to initial groups isn't difficult...like you said, you can use the random number generator to do that if you need to. The problem is trying to get Excel to remember all the previous possible combinations and eliminate them from any future combinations.

What you're looking to create is similar to cyclical movements in bridge tournaments - also called Howell movements. What this basically means is that one person remains in each group while everyone else moves to the seat number that is one lower than the one they were first allocated. So random number 8 would move to 7, 7 to 6, 6 to 5 etc. Whoever started with number 1 would go to position 17. The sticking point is the person having to stay put - you probably don't want them to sit through the same session again.

You could try using the Random Number Generator that's built into Excel. You have to install it first using Tools, Add-ins and selecting the Data Analysis Pak. Once you've done that, click on Tools, Data Analysis and select the Random Number Generator. Now comes the tricky part as I suspect what you're looking for is to create a Poisson distribution - that is random numbers generated by the number of events in a given time. In this instance you can substitute time for the number of workshops so select that option from the Distribution drop-down. The number of variables is going to be 5 for your workshops, while the number of Random Numbers will be 13 for the number of participants. The Lambda value will be 4. Pressing OK will populate the first 13 rows of your spreadsheet with random numbers.

Note that they won't necessarily be unique and that some will be outside of your given ranges but you can simply allocate any numbers higher than 4 to group 4. Each row represents one of the delegates but it's still a messy and contrived way of doing it.

[Win2Win]

If you got the Dummies Guide to VBA, the first 100 pages would teach you enough on how to do it. It's only a few lines of code. Not sure if the new Office has VBA.Net, but you could use the nice new Arraylist object for handling data :) The first decent addition .Net has.

[John]

OK Glos many thanks for that, I'll install it shortly and have a play around... will report back with how I've got on. By the way, what does the Lambda value represent, or will this become clear?

If this doesn't work I'll try your idea Keith. Piggy sent some VBA tutorials to me the other day so I'll be able to conjure up something.

[GlosRFC]

Strictly speaking, a poisson distribution is a statistical way of allocating random events over time based on the event either occuring or not occuring within any given period of time. For example, you might want to try to work out how many people would visit this forum in a week so, for modelling purposes, you'd allocate a random number to determine whether they clicked on the link or not.

Lambda (λ) represents the mean number of events occuring, i.e. the average number of people that would enter the site per hour or day. So during a given period of time (λt) your samples will either enter or not enter. Random numbers can therefore be allocated over a longer period of time according to the formula λΔt (the probability that the event will or won't occur in a given time period over a change in time). In your example, the mean number of events is going to be 4 groups.

Personally, I still think a piece of paper is going to be easier.