+ Reply to Thread
Results 1 to 5 of 5

Looking for Expense Splitting with Allocation Levels

  1. #1
    Registered User
    Join Date
    08-14-2012
    Location
    Canada
    MS-Off Ver
    Excel 2016
    Posts
    23

    Looking for Expense Splitting with Allocation Levels

    Hi,
    I have Excel 2016. I know there are lots of expense splitting templates. But I am looking for an additional feature and hope there is something like that out there.

    So, we have a group of families that are equally splitting picnic costs. But there is a single person, and we agreed that person should pay half the costs. I am doing the accounting, so I need something where I can put all expenses but this person bears only 50% of other families.

    As a workaround, I can list other families twice, but would prefer if there was something cleaner that allows me to enter 50% for that person.

    Here is one template I got from Internet that I am trying to modify to fit my requirement.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,703

    Re: Looking for Expense Splitting with Allocation Levels

    Trying to understand: In your worksheet, line 5 has a contribution of $100. So are you saying on that line, Person1 should pay $50, and Families 1 and 2 should each pay $25?

    If so, supposing the $100 is in cell D5, then
    Please Login or Register  to view this content.
    Is that what you're wanting?
    I know I'm not stupid, but I suspect I'm a lot stupider than I think I am

  3. #3
    Registered User
    Join Date
    08-14-2012
    Location
    Canada
    MS-Off Ver
    Excel 2016
    Posts
    23

    Re: Looking for Expense Splitting with Allocation Levels

    Quote Originally Posted by jomili View Post
    Trying to understand: In your worksheet, line 5 has a contribution of $100. So are you saying on that line, Person1 should pay $50, and Families 1 and 2 should each pay $25?

    If so, supposing the $100 is in cell D5, then
    Please Login or Register  to view this content.
    Is that what you're wanting?
    line 5,6,7 is where each family contributes to a fund. This contribution is $100 for family 1 & 2 and $50 for person 1. Somewhere I need to tell the system (Excel) that person1 has 50% weightage. the way i want to do this is by putting y instead of x. But other ways are welcome.

    The fund then pays for every expense. Every expense will be shared in such a way that person 1's weightage is half that of family. So basically, every expense gets divided into 5 parts, 2 parts for family 1, 2 parts for family 2 and 1 part for person 1.

    Then, after all expenses are done, there will be some money remaining in fund. This fund also gets divided in such a way that families have weightage double that of person.

    A simple summary is person is half of a family.

  4. #4
    Registered User
    Join Date
    07-10-2021
    Location
    GENEVA
    MS-Off Ver
    EXCEL FOR MAC MICROSOFT 365
    Posts
    45

    Re: Looking for Expense Splitting with Allocation Levels

    I have looked at your spreadsheet and even with your explanation above I am struggling to understand exactly what you want to achieve. If all you want is for the "x" to pay 100% and the "Y" to pay 50%, then the simple solution is to count the "x"s, and add the "y"s/2. So if there are 4"x" and 1"y" the total would be 4.5. Now simply divide your expense total (e.g.$100) by 4.5. The result is $22.2222. So each "x" should pay $22.2222 and each y should pay $11.1111. Have I missed something in your problem?

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,097

    Re: Looking for Expense Splitting with Allocation Levels

    As others have said it is hard for me to understand the desired result, however here is a proposal based on my guess:
    1. Put the x's and y's in row 3
    2. Populate the Family/Person columns using: =IF(OR($B5="",$B5="Contribution"),"",IF(J$3="x",$D5*2/$H5,$D5*1/$H5))
    3. Populate the Fund column using: =IF(B5="Contribution",D5*H5/2,"")
    4. Populate the Total Debt/Income cell using: =SUMIFS(J26:Z26,J4:Z4,"Fund")-SUMIFS(J26:Z26,J4:Z4,"<>Fund")
    5. Sum the Family/Person/Fund columns using: =SUM(J5:J24)
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Cancel splitting column and return splitting to general
    By Ali-M in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-10-2022, 12:29 AM
  2. Nested levels of 61 giving error as over 64 levels
    By RJK in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 05-14-2020, 01:07 PM
  3. Lookup & Expense Allocation Formula
    By chrapm1 in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 01-02-2018, 01:42 PM
  4. Sum-up weekly allocation to Monthly Allocation
    By juan.doe in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-11-2017, 04:41 AM
  5. Group Expense Splitting into couples
    By stacer12219 in forum Excel General
    Replies: 5
    Last Post: 05-02-2017, 10:32 AM
  6. Splitting an expense
    By Amature Amy! in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 07-25-2006, 02:15 PM
  7. Replies: 3
    Last Post: 09-13-2005, 06:05 PM

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