Assigning tasks to team with each task having specific weights

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I'd probably use Solver tool for that - see attached file.

columns A and B are task names and task weights.

in columns C:E there are 1/0 marks if particular person (row 1) is assigned to given task (these are the changed values in Solver)

I used all 0 as atarting point - no persons assigned to any task

In column F we have a sum of assignments (and it has to be 1 for each task, as only one person can be assigned to a task - this will be one of the restrictions in Solver, The other is that any assignment (C2:E11) has to be binary - either 0 or 1= nt assigned/assigned.

In row 12 is a sum of weights for a given person (using SUMPRODUCT formula)

In row 13 is a measure how this sum is different from average of sums for all persons (notice I've used squared value to: get rid of the sign - as we don't mind if a value is larger or smaller - we are only interested in if it's different and square to make higher differences to have even more impact)

In F13 we have a sum of these measures. We try to minimize it. In ideal case all persons would have the same weighted sum, so all values in row 12 would be the same and then all values in row 13 will be equal 0.

After running solver we are pretty close to that point - and probably the discreptancies (in my case total weights for Alex Dan and Mich were 7,9 7,8 8,2 - pretty close I think, althought Mitch can complain a bit) are acceptable



PS. If in real life you have only 3 persons and only 10 tasks, then youy have 3^10 = ca. 60 000 possible assignments - a brute force approach (testing any possible assignment could be used to finbg an optimal one. But if there is "just a bit more", say 7 persons and 12 assignments, we shall check more than 13 000 000 000 possible assignments - then for sure approximate approach as offered by solver is wortth considering

Hi see the above post - the layout is a bit different - staff is in one row, not in one column, but a result shall be useful. Note that yoyu can run SOlver again (may be few times) and the result could improve (or could not change, if it is already the optimal one).

For sure we are not able to obtain ideal allocation of tasks, as every person shall have 7,966666667 assigned, while weights are given only with 0.1 precision.
So the best possible is that 2 persons have total weight of 8 and third 7.9 And yes, it can gbe achieved see attached file