I have hundred of customers and 5 staff working on 3 types
of product. I am going to put the customers into 5 groups
and each staff responsible for one group. Considering the
workload of each staff should be similar to each other and
try to keep them specialized in product. Any analysis tool
in excel can help?

Pivot Table
Customer Product A Product B Product C Total
IBM 345 25 14 384
3M 23 23
Dell 456 67 523
HP 187 90 277

Hi,

The answer is yes, you can use solver to do this, but it isn't quick to set
up.

What you need to do is have five columns, one for each group, and constrain
in solver the sum of each row to be 1 and each cell to be 1 or 0 (so two
constraints -- one between 0 and 1 and the other to be an integer).

Then you can use SUMPRODUCT between each column and whatever measure of the
workload you have to calculate the workload for each group and then minimise
something like (not in perfect Excel)
SUM(ABS(workload - average (workload)))
which tries to get each workgroup similar.

If you want to add something on specialisation then you can do the same
SUMPRODUCT to generate the workload per product for each group and then you'd
want to maximise something like
SUM(workload per product per team ^ 2)
So minimising
SUM(ABS(workload - average (workload))) - SUM(workload per product ^ 2)
would have the overall effect you want.

To change the emphasis then just multiply one or other of the expressions by
something (so if the first part is more important, multiply it by 2 say).

I hope that this makes sense. I have to say I have no idea how long solver
would take to do this (I think there is a limit on the number of cells you
can have it change), at which point you have to use more than solver. Try
searching on
integer.programming VBA
on Google.

HTH

David Jessop

"KC" wrote:

I have hundred of customers and 5 staff working on 3 types
of product. I am going to put the customers into 5 groups
and each staff responsible for one group. Considering the
workload of each staff should be similar to each other and
try to keep them specialized in product. Any analysis tool
in excel can help?

Pivot Table
Customer Product A Product B Product C Total
IBM 345 25 14 384
3M 23 23
Dell 456 67 523
HP 187 90 277