Not sure if Excel Solver or Formulas can do solve this problem

I have a list of 47 employees

Each employee has a performance score with a max of 1164 and min of 95. The average score is 743 over the 47 employees.

I have 7 supervisors that I need to distribute these 47 employees to. Three of the supes will be assigned 6 employees and four of them will be assigned 7 employees to create 7 teams. The plan is to have Excel distribute the employee list based on their scores with conditions of each team score to be as close to each other as possible(im thinking the average of 743)

I really dont know where I would start with Excel Solver or if it will even help me and am not really sure I can create a formula that will address the above conditions.

Ive provided the workbook for anyone to check out. If you have any questions please feel free.

Thanks for your help.

A simple distribution algorithm would be to start off allocating the top scorer and the lowest scorer to the first supervisor, and then the 2nd-highest and 2nd lowest to the next supervisor and so on for the 7 supervisors, and on the next round allocate the top-8th and lowest-8th to supe_7, then the top_9th and bottom_9th to supe_6 and so on back down to supe_1, and continue this pairing on the final round to give each supervisor 6 team members. That should account for 42 employees, and the final 5 could be allocated to the supervisors who are to have an extra team member.

I'll have a think about how to implement this using formulae while I have something to eat. Of course, depending on how skewed the scores actually are, this may still give rise to some variations in the average for each team.

Hope this helps.

Pete

Great headstart, I'll get to working on that manually and see what i can come up with.

Thanks

Originally Posted by Clooney003

I have a list of 47 employees [....] Three of the supes will be assigned 6 employees and four of them will be assigned 7 employees

FYI, 3*6 + 4*7 = 46, not 47. But, in fact, you have 2 teams of 6 and 5 teams of 7; and 2*6 + 5*7 = 47.

Your error is: =DCOUNT($A$5:$O$51,15,A1:A2) should be =DCOUNT($A$5:$O$52,15,A1:A2), for example.