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.

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.

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?