I need to divide multiple accounts among several account reps. Each account
has a revenue amount, and each account rep should have a number of accounts
with a near equal total of revenue. All reps do not have to have the same
number of accounts. The spread should be based on total revenues. Any help
would be appreciated. Thanks

You could make a list of all the accounts and then sort it into
ascending order. Name this list "accounts"

Say you have only 2 reps.

assign large(accounts,1) large (accounts,3) etc to rep1
assign large(accounts,2) large (accounts,4) etc to rep 1

in other words you are assigning alternate accounts to the 2 reps.

This does not guarantee equality, but you could fine tune manually, eg
if one has 250000 more, look for a pair of accounts to exchange


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=520060

There are three steps to perform the distribution:

1. Divide the total revenue by the total number of reps. This gives the
ideal solution, the goal. The goal probably cant be reach perfectly, but
move on the next step.
2. Sort all the accounts descending so the first account in the list will
have the largest value.
3. Beginning with the first rep., start assigning accounts until just before
the goal has been reached for the rep. (stop before going over goal).
Continue for each rep. Until the list of accounts has been exhausted.

--
Gary's Student


"bf44qv1" wrote:

I need to divide multiple accounts among several account reps. Each account
has a revenue amount, and each account rep should have a number of accounts
with a near equal total of revenue. All reps do not have to have the same
number of accounts. The spread should be based on total revenues. Any help
would be appreciated. Thanks