Calculate Who Owes What from a Joint Bank Account

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.

IF(Person, LEN(Person) - LEN(SUBSTITUTE(Person, ",", "")) + 1)

I’m sorry … I think this has gone over my head. Where do I put the IF(Person)… formula you gave me?

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.

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.

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?

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.

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.

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.

has anyone tried using a separate table for each person’s contributions and then linking it all to a master transaction log? I’m curious if that might make calculating who owes what a bit easier, especially if you’re dealing with irregular expenses or split percentages. Would love to hear how others are managing this now.

Hey ​@bharatana!

Do you mean having two contribution tables? 
E.g.
Mike’s Contributions table?
Peter’s Contributions table?

That is not relational database best practices. Did you stumble upon a challenge on a similar use case as the one described on this post? If so, do you have any question I can help you with? 

Mike, Consultant @ Automatic Nation 
YouTube Channel

has anyone tried using a separate table for each person’s contributions and then linking it all to a master transaction log?

Kinda, I ended up with an ‘Expenses’ table that had a link to the Transaction table, and so maybe the Expense record was for $100, it’d be linked to one Transaction record per person, e.g. one for 30, one for 70, to roll up to 100

You could potentially set the percentage per person on the Expenses table and then use lookups to see how much is still owed?  Would be hardcoded though