Divide and Distribute Costs to Multiple Participants Based on Individual Amount Owed.

I am trying to create a sheet that will properly distribute proportional costs of a vacation to all families. Throughout the vacation, random family members pay for various group items such as rentals, gas, meals, etc. I have been able to calculate how much each family has paid and how much each family owes.

I need to figure out how to divide the amount one family owes and pay it to other families that are owed. To reduce the number of checks that are writen, it would be best to have the family OWING the most pay the family who is OWED the most.

In the example below, Families 1 & 5 are owed money. Families 2 & 3 did not attend and therefore owe nothing. Families 6 through 10 owe varying amounts to the other two families. EACH VACATION HAS A VARIABLE NUMBER OF FAMILIES, FAMILIES OWED AND FAMILIES OWING.

It would be best to have the family OWING the most, pay the family OWED the most and then continue to the next family. As such, Family 10 owing 509.00 would pay Family 5, then Family 6 woud pay, then Family 7 pay, then Family 9 would pay the balance, with the remainder paid to Family 1.

I want to display results in a grid like shown below.

How can this be accomplished?

Thanks in advance for your efforts and insights!
Rog




Please Pay -> Family1 Family2 Family3 Family4 Family5 Family6 Family7 Family8 Family9 Family10
Family1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Family2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Family3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Family4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Family5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Family6 0.00 0.00 0.00 0.00 0.00 342.00 0.00 0.00 0.00 0.00
Family7 0.00 0.00 0.00 0.00 0.00 294.00 0.00 0.00 0.00 0.00
Family8 71.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Family9 131.00 0.00 0.00 0.00 0.00 163.00 0.00 0.00 0.00 0.00
Family10 0.00 0.00 0.00 0.00 0.00 509.00 0.00 0.00 0.00 0.00


Family1 Family2 Family3 Family4 Family5 Family6 Family7 Family8 Family9 Family10 TOTALS
Total Paid 300.00 0.00 0.00 0.00 1,700 50.00 0.00 125.00 0.00 275.00 2,450.00 Total Spent
Family Members 1 0 0 0 2 2 2 2 2 4 15 Total Vacationers
Family Days 1 0 0 0 4 4 3 2 3 8 25 Total Family Days
Cost Per Family 98.00 0.00 0.00 0.00 392.00 392.00 294.00 196.00 294.00 784.00 98.00 Daily Cost per Vacationer
Due (Owed) (202) 0.00 0.00 0.00 (1,308) 342.00 294.00 71.00 294.00 509.00 0.00

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.