distributing values to make them equal

This is almost as much a math problem as an excel question.

Each person starts with a certain amount of loans to do. When someone is absent, those loans need to be distributed to others so that everyone winds up with as close to the same amount of loans that can be without taking away the number of loans they started with.

For example: Matt has 30 loans, John has 10 and Mary has 2 . Taco is sick leaving 20 loans. I need the spreadsheet to distribute 6 to John, 14 to Mary and none to Matt. So John and Mary have the same amount and Matt has none taken or given.

A problem I am running into is that it always distributes loans to Taco who is absent. If someone has a zero for loans in the Current Workload column, they are absent and shouldn't be counted.

You can see where I got stuck in this spreadsheet.
The work each person is starting with is on the Current Workload. The number to be distributed is # of Additional Loans. I want in "# to Balance" column to list how many loans need to be given to each person to make everyones workload as even as possible without subtracting from Current Workload.

If you even know how to accomplish a piece of this, please let me know. All contributions are very appreciated.

Hi

see if the attached gives you some ideas. It doesn't balance properly, as it will over estimate the available work to be reallocated, but may get you going.

rylo