Forum Discussion
How to balance an expense sheet with different percentages
Hello! I am trying to keep a balanced expense sheet for my household where one person is responsible for 55% of expenses and the other responsible for 45% of expenses. I also want to have the ability to build repayments to rebalance in the same table.
--
I am having difficulty figuring out how to balance the spreadsheet when I add in repayments.
This is supposed to track who owes who how much. This generally works but the equation in the 'Owed' table on the 'Analysis' sheet only works in person 2 owes person 1 money. When person 1 owes person 2 money the equation breaks.
I know Sumif functions are generally avoidable with pivot tables but, I am having difficulty figuring out how to make them work with my data.
I have attached a copy of the sheet with some data to demonstrate the issue. I have also commented describing the purpose of every table and column in the sheet.
Any guidance would be greatly appreciated!
This link takes you to an example (in the form of a Google Sheet) of how my wife and I work things out for mixed family get togethers for a week each summer. There are, as you'll see, five "family units," some units consisting of two people, some of one, one of four. We also recognize that some of the four member units contain a child or two below drinking age, so pro-rate the wine and beer differently than we do food. Or excursions to, say, mini-golf, which don't involve everybody, get their own sheets.
My example doesn't work by percentages, as you're asking, but does still illustrate a different way, slightly different, from the way you're trying to track and calculate.
The other thought that occurred to me, though, was that it might be cleaner and less problematic if you created separate checking and credit card accounts for those shared expenses, and--sticking with the checking account example for the time being--each contributed (for simple illustration purposes) 550 and 450 dollars at the start of each month, or whenever additional funds were needed. Then make all payments out of that already apportioned balance. You'd just keep the fund from which payments were made filled at that 55/45 ratio. In short, account for the different contribution rate up front, not after the fact. That would do away with trying to track each expense and figure out who owes whom what.
mathetesThank you for the time and energy req for your thoughtful reply! I look forward to reviewing your ex to learn how you went about it. However, I am unable to view the sheet shared through the link. Do you mind changing the sharing permissions or the equations you used?
Thank you,
Josh
Here that same example is, as an Excel workbook. You should have no problems accessing this.
Let me know if you have any questions.
