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

1. ## 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?

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

2. ## Re: Divide and Distribute Costs to Multiple Participants Based on Individual Amount Owed.

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.

3. ## Re: Divide and Distribute Costs to Multiple Participants Based on Individual Amount Owed.

Thanks FDibbins!

4. ## Re: Divide and Distribute Costs to Multiple Participants Based on Individual Amount Owed.

Must it be that complicated? Why not select one in the group to be the treasurer and sett up a balance sheet similar to this?

Alf

5. ## Re: Divide and Distribute Costs to Multiple Participants Based on Individual Amount Owed.

Your solution is simple, and it essentially does what is needed. And we already do it that way. However, it's just not as FUN! This spreadsheet adventure was to hopefully create a workbook wherein each family could simply email their completed attendance and receipt sheet to the "treasurer". The treasurer could add the sheet to the workbook and it would spit out a pay-list for each family. Decentralizing the payments is the desired result.

Since there are a variable number of families V(f), ususally between 5 and 20, on each vacation and a variable number of family members V(m) atttending for a variable number of days V(d) per vacation, and variable costs per family V(c), it would be nice to quickly calculate each family's fair share for their time on vacation. Hence the "complicated" grid.

Any insights as to how to accomplish such a foolish endeavor would be appreaciated!

Cheers!
Rog

Originally Posted by Alf
Must it be that complicated? Why not select one in the group to be the treasurer and sett up a balance sheet similar to this?

Alf

6. ## Re: Divide and Distribute Costs to Multiple Participants Based on Individual Amount Owed.

Ok let's take this as an intellectual challenge and see if it's possible to solve.

I will have another go at it but complex formulas are not my speciality I'm more of a macro man.

Alf

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1