8

I have 100 objects ("candies") that I need to distribute between five people so that each has an equal number of candies (in this case, 20 candies per person).

However, each person has also expressed their preferences of candy to me in a chart, similar to below. Top-favored candies receive 10 points, least-favored candies receive -10 points, and neutral-favored candies receive 0.5 points.

enter image description here

I need to sort the items out so that:

  • Each person receives the same number of candies
  • Each person's total "satisfaction" (points) is maximized
  • My output is a list of each person's assigned items

I'm familiar with Excel's in-house Monte Carlo simulation tools (Solver, F9 diceroll, etc) and would like to stick to those tools. While I know how to set up the chart, and how to use the column summation to input into Solver, I don't know how to get it to give me the desired output. Furthermore, how do I adjust the solver so it takes into account individual preferences rather than empirical ones?

To wit: how do I begin setting up this model?

8
  • Note: this is not my homework; it's for a project I'm working on, which is proprietary information, hence the stand-in of "candies" as the object being sorted.
    – Aarthi
    Aug 28, 2012 at 21:42
  • 1
    Maybe you should try on: math.stackexchange.com and get some mathematicians to help you out, didn't have the option to suggest moving their through the flag.
    – Eric G
    Aug 28, 2012 at 23:05
  • @EricG Normally, I would have asked on Math, except I'd like to do it programmatically in Excel. Hey, if it's better on math, that's 100% legit and I'll cheerily cart it over.
    – Aarthi
    Aug 29, 2012 at 1:34
  • I tried to be helpful by asking math.stackexchange.com/questions/188124/… (posted it right around the same time as you posted this due to you asking it in Root Access chat). Apparently at least one math person was also stumped by the problem definition itself... Aug 29, 2012 at 2:10
  • 1
    If constraints can be put on the number of individual candies that are available, then the solver should be able to produce a more even result. you can use the same type of options that I show in my start picture, you would then add a sum line to each line of candy counts, and use the constraint that the SUM cell must be a specific value (or less)
    – SeanC
    Aug 29, 2012 at 16:12

1 Answer 1

3

The only way I can seem to get Excel Solver to produce reasonably distributed selections, is by using a constraint on the number of candies available

From my comments, What's to stop someone just picking 20 of whatever is their maximum rated candy? that way, no one has any of their minimum choice.

Here's the result of testing using solver - it did what I expected. start and result

When I add an additional constraint, by restricting the candy count, the distribution is more varied, but most selections will have up to the maximum amount of candies available.

By using this: even distribution the answer becomes a varied choice of candy, but with most people getting their top pick

For your perusal, this is how I set up the spreadsheet for the solver to work

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .