+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    10-26-2012
    Location
    California
    MS-Off Ver
    Excel 2007, 2010
    Posts
    13

    Question 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
    Attached Files Attached Files
    Last edited by rkenchel; 10-26-2012 at 06:44 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,990

    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.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    10-26-2012
    Location
    California
    MS-Off Ver
    Excel 2007, 2010
    Posts
    13

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

    Thanks FDibbins!

  4. #4
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,657

    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
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-26-2012
    Location
    California
    MS-Off Ver
    Excel 2007, 2010
    Posts
    13

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

    Thank you VERY much for your reply Alf!

    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

    Quote Originally Posted by Alf View Post
    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. #6
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,657

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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