Hello, I'm using the Shared Expenses template. The sheet is great, except there's no way to pay down the balance from one person to another. How could I, on a case-by-case basis, edit the last column (Expense Per Person) so that it does not split the amount, but rather applies it as a payment from one user to another?
MacBook Pro 15″, OS X 10.11
A while ago someone wanted the template modified so that an expense could be assigned to a single person vs shared amongst them all. I've expanded that modification a little more to be able to make payments from one person to another. If you don't need all that, you may be able to see from the formulas how I handled payments between people and you can modify the original template to handle it.
I'm not a big fan of columns G-L (in my version of the template) of the People table. It seems like an odd way to pay back the money vs throwing it all in one pile then divvying it up, or some other method where whole numbers are involved. Personally I would delete them.
I forgot to fix the chart before taking my screenshot. It inadvertently has column E (payments received) as a second series. It should just be column F (Owe or Due).
In the Expenses column, the "-" followed by a name indicates an expense that is solely the responsibility of that person. Someone else may have paid for it.
Payments between people is straightforward. Just put a name in the "Paid to" column. I'd leave the Expenses column blank.
Formulas in Expenses table:
F2 =IF(OR(COUNTMATCHES($A2," - ")>0,D2≠""),0,E2÷COUNTA(People::A))
fill down to complete the column
Formulas in the People table
H1 =OFFSET($A$1,COLUMN()−COLUMN($H)+1,0)
Fill to the right to complete the row
B2 =IF($A2="","",SUMIF(Expenses::C,A2,Expenses::E))
C2 =IF($A2="","",SUMIF(Expenses::A,REGEX(" - "&$A2,FALSE),Expenses::E))
D2 =IF($A2="","",Expenses::F$8)
E2 =IF($A2="","",SUMIF(Expenses::D,$A2,Expenses::E))
F2 =IF($A2="",0,C2+D2−B2+E2)
G2 =IF(F2<0,F2÷SUMIF(F,"<=0"),0)
H2 =IF(OR(H$1="",$A2=""),"",MAX(XLOOKUP(H$1,$A,$F)×$G2,0))
Fill down with all those to complete the columns
Select H2:L6 and format them with the following custom format and highlighting rules. You can Copy Style, Paste Style from one of those cells to the ones in column F.
If you find any problems with it, please let me know. I have not tested it extensively.
A while ago someone wanted the template modified so that an expense could be assigned to a single person vs shared amongst them all. I've expanded that modification a little more to be able to make payments from one person to another. If you don't need all that, you may be able to see from the formulas how I handled payments between people and you can modify the original template to handle it.
I'm not a big fan of columns G-L (in my version of the template) of the People table. It seems like an odd way to pay back the money vs throwing it all in one pile then divvying it up, or some other method where whole numbers are involved. Personally I would delete them.
I forgot to fix the chart before taking my screenshot. It inadvertently has column E (payments received) as a second series. It should just be column F (Owe or Due).
In the Expenses column, the "-" followed by a name indicates an expense that is solely the responsibility of that person. Someone else may have paid for it.
Payments between people is straightforward. Just put a name in the "Paid to" column. I'd leave the Expenses column blank.
Formulas in Expenses table:
F2 =IF(OR(COUNTMATCHES($A2," - ")>0,D2≠""),0,E2÷COUNTA(People::A))
fill down to complete the column
Formulas in the People table
H1 =OFFSET($A$1,COLUMN()−COLUMN($H)+1,0)
Fill to the right to complete the row
B2 =IF($A2="","",SUMIF(Expenses::C,A2,Expenses::E))
C2 =IF($A2="","",SUMIF(Expenses::A,REGEX(" - "&$A2,FALSE),Expenses::E))
D2 =IF($A2="","",Expenses::F$8)
E2 =IF($A2="","",SUMIF(Expenses::D,$A2,Expenses::E))
F2 =IF($A2="",0,C2+D2−B2+E2)
G2 =IF(F2<0,F2÷SUMIF(F,"<=0"),0)
H2 =IF(OR(H$1="",$A2=""),"",MAX(XLOOKUP(H$1,$A,$F)×$G2,0))
Fill down with all those to complete the columns
Select H2:L6 and format them with the following custom format and highlighting rules. You can Copy Style, Paste Style from one of those cells to the ones in column F.
If you find any problems with it, please let me know. I have not tested it extensively.
Could you give an example of what you mean?
SG
Are you able to provide this updated template? I can't seem to figure out how to make these modifications...
Numbers Shared Expenses Calculation Modification Question
