Distribute orders with avearge for designers

I'm not a pro at Excel and I need help with it!
I need to assign orders to designers (60 Designers) daily; the daily order count is above 800.

My requirement is I need to assign the average count to each designer with Minimum and Maximum count.
In the below example, there are 6 designers on the left side to whom I need to assign orders and to make sure that their count should not be more compared with each other. As there is a big order with 32 counts that should be assigned to a designer, I need to make sure that the same designer should not be assigned to another order as his workload will increase as compared with others.

Could you please help me create a formula using average, minimum, and maximum to distribute the average count to the designers.

Thanks in advance

Assigning .xlsx

Originally Posted by kowon

What exactly do you need?
You have made a table with the sum of assigned counts, so you can see when some designer is crossing the maximum orders and you can assign different designers in the E column.

I need to assign a count equally or average to the designers. The table on the right side indicates to me if a designer has more count so that I can manually change his/her assignment.

Currently, I'm assigning manually and the count of designers is 60, and the order count is more than 600, so it takes about an hour for me to adjust the average and shuffling on a daily basis

If I could get a formula to set minimum, maximum and average number then it will really helpful for me!

have you tried a pivot table?

to assign average

that is what you see in column P

Been working on this for a while and this is what I was able to come up with. Perhaps someone can improve this, maybe by incorporating solver.
1. Put the designers in cells F1:K1
2. Populate cells F3:K16 using: =IF(OR(SUM(F$2:F2)=0,SUM(F$2:F2,$C3)<=$N$3),$C3-SUM($E3:E3))
3. Populate Q2:Q7 using: =SUM(INDEX($F$3:$K$16,,MATCH(P2,F$1:K$1,0)))
Note that this proposal requires the value in cell N3 be manually changed until all rows (F3:K16) have a value.
Note that I realize the it would be better if both designers 5&6 were assigned a count of six.
Let us know if you have any questions.

This principle could be applied to either file, but Alf's comes out with a more even distribution.
Produce a third table populated using: =IF(I6=1,$D6,"")
Let us know if you have any questions.

Hi JeteMc

i would say that my second matrix (S6:X19) is not needed. That I set up to calculate the average for each designer but what is the average? The average for each designer taking all values in the column or just the average of values the values that are chosen for the total count?

If the latter the the first four designers only has one value so what is the average of 32, or 16, or 12 or 6?. Designer 5 has 3 values to get an average from 4, 1, 1 gives 2 as average and Designer 6 has 7 values but they are all 1 so the average in this case is 1.

I liked you addition to the solver file and I do think the OP now got an answer to the posted problem.


Alf

Yes, the following formula could be used to populate column C of Alf's file (post #12): =INDEX(J$5:O$5,MATCH(1,J6:O6,0))
Note that based what Alf said in post #15 the calculations made in cells S6:X19 are not needed to accomplish what you want.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.