Forum Discussion
How to balance an expense sheet with different percentages
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.
mathetes Thank you for sharing this! I guess the question I have boiled into one sentence is:
Is there a function or formula that I can use to balance an expense sheet with repayments from 2 sources.
I was not able to find an equation that did this in the shared spreadsheet. I currently use the following functions:
Value 1: =SUMIFS(tblTransactions[Amount],tblTransactions[Person],'Data Validation'!E2,tblTransactions[Category Type],'Data Validation'!C2)*-1 [I use this function to sum the total expenses per person then multiply by -1 to return a positive value]
then
=SUMIF(tblTransactions[Category Type],"Expense",tblTransactions[Amount])*-1 [I use this function to sum the total expenses between both people in the table, then multiply by -1 to return a positive value]
then
Value 2: I multiply the the value returned above by .55 for person 1 and .45 for person 2 to return the value each person is responsible for]
then
Value 3: =SUMIFS(tblTransactions[Amount],tblTransactions[Person],'Data Validation'!E2,tblTransactions[Category Type],'Data Validation'!C10) [I do this to sum the total payments made by person 1 to the other to balance the expense sheet]
then
I subtract Value 1-Value 2-Value 3
This only works if person 1 pays person 2. When person 2 pays person one the logic fails.
Any guidance would be greatly appreciated!
- mathetes So I just used the ABS function instead of multiplying by -1 and that seemed to solve the error in the calculation. Not sure why but, I am grateful for all of the time you spent on this! Thanks again!