Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jan 15, 2022 10:07 AM
I’m setting up a base that is hopefully going to help me organize my finances with my boyfriend. We have a joint bank account, and I need to be able to show how much money is going in and going out. It seems like he is not leaving enough money to pay for his bills.
So, I’m wondering if I can calculate who owes what in the account. Like … I import the CSV of the bank account, then create a table for people (which would be just two), then add up the balances of all the expenses that are the responsibility of that person …
What about stuff that is both our responsibility … would I create another person and then divide that into two …
I’m so confused. :crazy_face: :frowning_with_open_mouth:
Jan 15, 2022 11:10 AM
I suggest linking to both people if there is shared responsibility. Use a formula field to find the number of linked people (assuming that {Person}
is the name of your link field):
IF(Person, LEN(Person) - LEN(SUBSTITUTE(Person, ",", "")) + 1)
Use another formula field to divide the record total by the number of linked people (or combine both into a single formula if you want), which will give you the amount owed per person. If it’s only linked to one person, the divided amount won’t change. For items where both are linked, it will be cut in half. (It’ll actually be a hair off in some cases because values with an odd number of cents won’t divide evenly, so maybe add the ROUNDUP()
function around the division calculation to round to the nearest whole penny, which will put you on the high side of half. Still not ideal, but there’s not much else you can do.)
Over in your [People]
table (whatever it’s named), you then roll up the total of that last formula using the SUM(values)
aggregation formula. Where one person is linked, the full total will go to that person. Where there is a shared responsibility, the halved amount will be added to both of your totals.
Jan 19, 2022 05:40 AM
I’m sorry … I think this has gone over my head. Where do I put the IF(Person)… formula you gave me?
Jan 19, 2022 06:33 AM
In a new formula field in the table where expenses are being tracked. Substitute “Person” with the reference to your link field that links to your [People]
table.
Jan 19, 2022 08:20 AM
Oh … gotcha … okay … that’s great. It then gives me a count of how many people are responsible for that transaction.
On to step 2 … thanks.
Jan 19, 2022 08:35 AM
Logistical Question …
The Table with the Transactions has the Expenses the Deposits … like this:
Date | Person | In | Out | Balance | Responsibility (step 1)
So in the formula to divide the amount by the Responsibility … should I Add the in and the out then divide by the Responsibility?
wait … then I’d have to add the - (negative) to all the out entries.
So like …
ROUNDUP ((in+out) / Responsibily )
Forgive my lack of syntax, I don’t know how to formulate the equation but is that would I should be trying to do?
Jan 19, 2022 10:05 AM
My read of your situation is that you’re only dividing the responsibility for actual expenses. For example, if there’s a $150 utility bill to be paid, that gets split evenly and you each pay your portion toward it. I didn’t get the impression that income was also to be similarly divided.
Jan 19, 2022 12:14 PM
Yeah, I have both, I wanted to make sure that everything balances out … so that all the deposits make up for the expenses … I’m sorry I wasn’t clear. it seems like there should be a template for this, but I looked but couldn’t find one. Maybe this is too basic for Airtable.
Jan 19, 2022 12:20 PM
Thanks for the clarification. In that case, I recommend using two formula fields in your main transaction table—one to calculate the split responsibility for {in}
values, the other for {out}
values—both using the count of linked people to divide the relevant number.
Over in your [People]
table, you’ll then have two rollup fields—one each to summaries the totals for {in}
and {out}
—and you can then use a formula field to do the rest of the math on those aggregate values.