This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Base Design
- Re: Calculate Who Owes What from a Joint Bank Acco...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

0
554
5

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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:

Reply

8 Replies 8

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 19, 2022 06:33 AM

`[People]`

table.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 19, 2022 10:05 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 19, 2022 12:14 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

Reply