Minimize Variance and Optimize

[mat1993]

I am currently working on a project to minimize variance and need some help to see if Excel can optimize this. The way I'm defining variance is (MAX-MIN)/AVG.

I have a set of 60 numbers being input into the spread sheet. These 60 numbers need to be separated into 10 groups of 6 such that the group with the minimum sum is as close to the group with the maximum sum as possible. In other words I am trying to minimize the variance between the Min and Max of these 10 groups by varying the combinations of the 60 inputs.

Does anyone know if this can be achieved by the Solver or any other way?

I really appreciate any input.

 

[Rijnsent]

Hi Mat,
that sounds indeed like an optimizer problem. I bumped into this howto, which does the same on a smaller scale: Using Excel and Solver for problem solving, a practical tutorial. | Chandoo.org - Learn Microsoft Excel Online
The issue I had is when I tried to apply it on 60 items, the solver gave back bogus. My manual approach led to better results: add a ranking column (=RANK) to your data. Assign the numbers like so (rank numbers):
Group A: 1, 20, 21, 40, 41, 60
Group B: 2, 19, 22, 39, 42, 59
Group C: 3, 18, 23, 38, 43, 58
etc. It's an approach, you could manually tweak it a bit, but the results looked quite okay (with a mockup data set).

Hope that helps,

Koen