# Looking for Expense Splitting with Allocation Levels

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

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

3. ## Re: Looking for Expense Splitting with Allocation Levels

Originally Posted by jomili
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. ## 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. ## 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.

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