Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Musical Chairs using Excel.?
I wonder if anyone can help me find a solution to this problem please?
I run training sessions for a growing group of about 50 volunteers approximately every 3 months. In order to help the learning process, I allocate them to tables of 6 or 7 with the aim of enabling them, over a period of time, to meet and work with as many of their colleagues as possible. For obvious reasons, I also try to ensure that each table has the widest possible mix of experience. To manage this, I have a spreadsheet which records in Column A the names of the volunteers (most experienced at the top, newest at the bottom). The subsequent columns record, from left to right, the table numbers that they sat on at each meeting. A blank indicates when an individual was unable to attend a session. My question is about to how best to allocate table numbers at future sessions. I have tried allocating places randomly but, interestingly, that does not always give the widest mix of experience on each table (you can, by chance, end up with virtually all the 'new' members on one table). Furthermore, I would like to ensure that no one sits on the same table as someone they worked with at the last session (or, preferably, the last 2 or 3 sessions). Has anyone any ideas please as to how I can solve this problem using Excel? TIA V |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Musical Chairs using Excel.?
Hi,
Probably the correct way to do this is to use the Solver feature The file name is SOLVSAMP.XLS. It is usually found in C:\Program Files\Microsoft Office 12\Samples It includes sample covering the following topic areas: Product Mix Shipping Routes Staff Scheduling Maximizing Income Portfolio of Securities Engineering Design (Electric circuits) the one that is probably applicable to you is Staff Scheduling -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Victor Delta" wrote: I wonder if anyone can help me find a solution to this problem please? I run training sessions for a growing group of about 50 volunteers approximately every 3 months. In order to help the learning process, I allocate them to tables of 6 or 7 with the aim of enabling them, over a period of time, to meet and work with as many of their colleagues as possible. For obvious reasons, I also try to ensure that each table has the widest possible mix of experience. To manage this, I have a spreadsheet which records in Column A the names of the volunteers (most experienced at the top, newest at the bottom). The subsequent columns record, from left to right, the table numbers that they sat on at each meeting. A blank indicates when an individual was unable to attend a session. My question is about to how best to allocate table numbers at future sessions. I have tried allocating places randomly but, interestingly, that does not always give the widest mix of experience on each table (you can, by chance, end up with virtually all the 'new' members on one table). Furthermore, I would like to ensure that no one sits on the same table as someone they worked with at the last session (or, preferably, the last 2 or 3 sessions). Has anyone any ideas please as to how I can solve this problem using Excel? TIA V |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Musical Chairs using Excel.?
"Shane Devenshire" wrote in
message ... Hi, Probably the correct way to do this is to use the Solver feature The file name is SOLVSAMP.XLS. It is usually found in C:\Program Files\Microsoft Office 12\Samples It includes sample covering the following topic areas: Product Mix Shipping Routes Staff Scheduling Maximizing Income Portfolio of Securities Engineering Design (Electric circuits) the one that is probably applicable to you is Staff Scheduling -- If this helps, please click the Yes button. Shane Thanks for that, I've installed the Solver add-in. However, can you please suggest how I should actually use it to solve this problem? Presumably I shall need a way of allocating tables and then a formula that counts how many people are sitting at the same table as someone else they sat with last time. And then the system needs to iterate until the total is zero (or minimised). V |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Musical Chairs using Excel.?
"Victor Delta" wrote in message
... "Shane Devenshire" wrote in message ... Hi, Probably the correct way to do this is to use the Solver feature The file name is SOLVSAMP.XLS. It is usually found in C:\Program Files\Microsoft Office 12\Samples It includes sample covering the following topic areas: Product Mix Shipping Routes Staff Scheduling Maximizing Income Portfolio of Securities Engineering Design (Electric circuits) the one that is probably applicable to you is Staff Scheduling -- If this helps, please click the Yes button. Shane Thanks for that, I've installed the Solver add-in. However, can you please suggest how I should actually use it to solve this problem? Presumably I shall need a way of allocating tables and then a formula that counts how many people are sitting at the same table as someone else they sat with last time. And then the system needs to iterate until the total is zero (or minimised). V I've got my head round this problem a bit more now, thanks to some help and suggestions. What it comes down to is that I need a way of identifying if two people sat on the same table at consecutive meetings. See the following table: Col A Col B Col C Col D Name Feb April Ann 2 3 Bill 1 6 Carol (Blank) 4 (Blank indicates Carol was not present at February meeting) Dick 2 3 What I need in Col D is a formula that indicates 'Y' in rows 3 and 6 to show that Ann and Dick sat on the same tables in both February and April. Can anyone suggest a way of doing this please? Array formula? Thanks so much, V |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
musical notes displayed in Word (offic 2003) | Excel Discussion (Misc queries) |