Unsolved problem - Simulation/VBA required?

phill at GE

New Member
Joined
Nov 25, 2005
Messages
4
Hi,

I need to put ~20 people in ~20 jobs in different locations. Each person gets to choose a favourite 3 locations (ranked). The aim to to place each person in one of their top 3 choices, and to minimise the average pick over the group.

Clearly this can be done by hand but the chances of selecting the best combination gets very slim with more people. Here is an example of what I am trying to do. (apologies for old HTML add-in but new one doesn't seem to be working for me)
rotation picker for mr.excel.xls
ABCDEF
1PhillNicolaMarcoAnniePavelCHOICES
2LondonParisLondonDublinParis1
3BoZDublinParisStavangerLondon2
4ShannonStavangerBoZLondonBoz3
5PhillNicolaMarcoAnniePavelPLACEMENT
6BoZDublinLondonStavangerParis
7
8Averageplacement1.6
Sheet1


Any solutions to this unresolved previous posting would be extremely welcomed!!!

Cheers - Phill at GE
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
And what if there is a situation where there is no solution? For example 4 people pick the same 3 locations?
 

phill at GE

New Member
Joined
Nov 25, 2005
Messages
4
Someone would be asked to revise their choices or a 'forced' placement might ensue.

To get round this it is usual that we ask for a 4th/5th favourite. The chances of 6+ ppl choosing exact same 5 placements are slim (especially given the nature of picking, but I won't go into detail)

Any ideas on the solution to this prob? I can't believe the collective genius on this board cannot slay this beast!!!

Best regards,
Phill
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
On other words, in the solution you are asking for, you'd want an indicator that all was not right when more than 3 people pick the same 3 places? ( so that you can then go and ask them to revise their choices, or ask for a 4th/5th favourite )

So are you going to want a method that will work with 4 and 5 choices too then?
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
Which would be the right choice

Looking at your examle, it has 2 solutions, here is the second:
placement.xls
ABCDEF
1PhillNicolaMarcoAnniePavelCHOICES
2LondonParisLondonDublinParis1
3BoZDublinParisStavangerLondon2
4ShannonStavangerBoZLondonBoz3
5
6PhillNicolaMarcoAnniePavelPLACEMENT
7BoZStavangerLondonDublinParis
823111choice
9Average placement1.6
Sheet1


So, which would be the correct one?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,112
Messages
5,570,259
Members
412,314
Latest member
yazanwael
Top