Even distribution of workload

Which is more important -- that they have the same number of files or customers?

Originally Posted by shg

Which is more important -- that they have the same number of files or customers?

Both are equally important. What matters is that the total number of files (ex: G21-K21) and the total number of customers (ex: G33-K33) are divided evenly so that each staff handles approximately the same amount of files (ex: 20-21 files) and the same number of customers (ex: 51-53).

EDIT: If you're asking this question in order to come up with the formula, I would say prioritize whichever is harder to distribute/adjust. Note that the number of customers is always higher than the number of files since one file can contain one up to a handful of customers.

Originally Posted by BlindAlley

How are the number of customers (B13) determined ?

Are they a constant formula or do they change daily ?

The # of Files (A2) and the Count (B13-B19) in my attached file change daily. The Count is the # of Files that have [different #] of customers. Since these numbers are based on the actual number of files we receive and the actual number of customers in each of those files, they cannot be manipulated. The # of Clerks is the only thing that's constant.

However, it doesn't matter if Clerks 1 and 2 handles:

• 8 files with 1 customer
• 4 files with 2 customers
• 2 files with 3 customers
• 2 files with 4 customers
• 3 files with 5 customers
• 1 file with 6 customers

while Clerk 3 handles:

• 7 files with 1 customer
• 4 files with 2 customers
• 3 files with 3 customers
• 3 files with 4 customers
• 3 files with 5 customers

and so on, so long as each of them worked on 20-21 files with 51-53 customers.

Many thanks to you both for taking the time to look at my problem.

I think this does what you want:

Row\Col	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2	Total								Customers						Files
3	101								Alan	Barb	Cain	Dana	Eric		Alan	Barb	Cain	Dana	Eric
4	Files	Cust/File	Helper		File #	Cust	Choose		0	0	0	0	0		0	0	0	0	0
5	1	7	0		1	7	Alan		7	0	0	0	0		1	0	0	0	0
6	2	6	1		2	6	Barb		7	6	0	0	0		1	1	0	0	0
7	12	3	3		3	6	Cain		7	6	6	0	0		1	1	1	0	0
8	13	4	15		4	3	Dana		7	6	6	3	0		1	1	1	1	0
9	15	5	28		5	3	Eric		7	6	6	3	3		1	1	1	1	1
10	18	2	43		6	3	Dana		7	6	6	6	3		1	1	1	2	1
11	40	1	61		7	3	Eric		7	6	6	6	6		1	1	1	2	2
12			101		8	3	Barb		7	9	6	6	6		1	2	1	2	2
13					9	3	Cain		7	9	9	6	6		1	2	2	2	2
14					10	3	Dana		7	9	9	9	6		1	2	2	3	2
103					99	1	Alan		52	51	51	51	51		19	19	20	21	20
104					100	1	Barb		52	52	51	51	51		19	20	20	21	20
105					101	1	Cain		52	52	52	51	51		19	20	21	21	20
106					102	#N/A	#N/A		#N/A	#N/A	#N/A	#N/A	#N/A		#N/A	#N/A	#N/A	#N/A	#N/A

See the last tab of the workbook at https://app.box.com/s/a83j0wt8dawtw1vwbdqdnqnzqs4qaurm

Wow! This looks awesome! I know it's a lot to ask but would it also be possible to adjust the formula so that the number of files that each staff is handling is proportionate to the number of customers? For example, based on the table above, Alan is handling 52 customers. Would it be possible to adjust the formula so that he also handles the most number of files (21 instead of 19)? If not, I totally understand.

Also, I'm a total noob when it comes to Excel so please forgive me for even clarifying this. Should the number of staff and/or files increase or decrease, I can just insert or delete columns under the customers (Column J-N) and files (Column P-T) sections and/or rows below and copy the formula, right? I tried it and it seemed to work but thought I'd ask anyway just to be sure.

Again, thank you so much for your time.

If not, I totally understand.

That's good!

Should the number of staff and/or files increase or decrease, I can just insert or delete columns under the customers (Column J-N) and files (Column P-T) sections and/or rows below and copy the formula, right?

Copy the formulas and adjust accordingly, yes.

You're welcome.

Great, you're welcome.

Hello shg! Sorry for bothering you again. Just wanted to know what you prioritized in the formula you provided before, files or customers? If you prioritized the customers and we want to prioritize the files, do we just switch the information in columns B and C and that's it? If not, how do we prioritize files instead?

Thanks in advance.

Rhetorical question: How does the switch in your bedroom know it's supposed to turn on the overhead light, just because it says "overhead?"

Answer: It doesn't. It's wired to the overhead light, and the label is for your convenience.

Rhetorical question: How does Excel know which numbers are files and which numbers are customers?

Answer: It doesn't. Those are just labels that indicate to you the meaning of the data.

So it you swap the file data and the customer data (and reverse the labels, just for your own understanding), you will now be prioritizing the opposite of what you were previously.

Row\Col	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
2	Total		Link						Files						Customers
3	101		EF1093061						Alan	Barb	Cain	Dana	Eric		Alan	Barb	Cain	Dana	Eric
4	Files	Cust/File	Helper		File #	Cust	Choose		0	0	0	0	0		0	0	0	0	0
5	1	7	0		1	7	Alan		1	0	0	0	0		7	0	0	0	0		J5: = relN + ($H5 = J$3)
6	2	6	1		2	6	Barb		1	1	0	0	0		7	6	0	0	0		P5: = relN + $G5 * ($H5 = P$3)
7	12	3	3		3	6	Cain		1	1	1	0	0		7	6	6	0	0
8	13	4	15		4	3	Dana		1	1	1	1	0		7	6	6	3	0
9	15	5	28		5	3	Eric		1	1	1	1	1		7	6	6	3	3
10	18	2	43		6	3	Alan		2	1	1	1	1		10	6	6	3	3
11	40	1	61		7	3	Barb		2	2	1	1	1		10	9	6	3	3
12			101		8	3	Cain		2	2	2	1	1		10	9	9	3	3
13					9	3	Dana		2	2	2	2	1		10	9	9	6	3
14					10	3	Eric		2	2	2	2	2		10	9	9	6	6
15					11	3	Alan		3	2	2	2	2		13	9	9	6	6
16					12	3	Barb		3	3	2	2	2		13	12	9	6	6
17					13	3	Cain		3	3	3	2	2		13	12	12	6	6
18					14	3	Dana		3	3	3	3	2		13	12	12	9	6
19					15	3	Eric		3	3	3	3	3		13	12	12	9	9
20					16	4	Alan		4	3	3	3	3		17	12	12	9	9
21					17	4	Barb		4	4	3	3	3		17	16	12	9	9
22					18	4	Cain		4	4	4	3	3		17	16	16	9	9
23					19	4	Dana		4	4	4	4	3		17	16	16	13	9
104					100	1	Eric		20	20	20	20	20		55	53	53	48	48
105					101	1	Alan		21	20	20	20	20		56	53	53	48	48
106					102	#N/A	#N/A		#N/A	#N/A	#N/A	#N/A	#N/A		#N/A	#N/A	#N/A	#N/A	#N/A
107					103	#N/A	#N/A		#N/A	#N/A	#N/A	#N/A	#N/A		#N/A	#N/A	#N/A	#N/A	#N/A

You're welcome.