Creating a trip planning doc and need to find a way to assign cost per person

I found this doc that allows people to see trip totals but I’d like to dig a little deeper and have a tally of how much each person owes to who, not just totals. How could I go about doing this?

Here’s the file I’ve been working in.


It looks like you’re making good progress here (your Things That Cost Money table which logs who paid and who “should have” paid aka Shared Between).

It looks like you’re trying to get a more granular idea of who owes how much to whom, but that doesn’t really matter right? Say you have the following balances at the end of it all:

Alfred $40
Betty $50
Charles -$10
Dora -$80

The nagatives just need to pay their balances to the positive people in whatever split is most convenient (the past history of what was split with whom doesn’t matter anymore, as it’s already reflected in the balances).

What might be cool though is a little algorithm that makes everyone whole using the fewest possible transactions. (In the above case, 3 transactions is the minimum: Dora’s going to have to pay 2 people no matter how you slice it). Though in most trip situations, you can probably just eyeball this pretty quick…

Interested to see how this doc evolves though. This is a pretty common need and I think it would be helpful as a published doc others can clone.

Another thing I like to do in situations like this (via spreadsheets, in the past) is log the “paying back” transactions in the ledger so everyone can see how their balance gets brought back to neutral.

Hi @Jp_Davidson :blush: and welcome to the Community :partying_face: !

Edit: :sweat_smile: As I’ve completely misread your question, I’m going to withdraw my reply for now :upside_down_face:

(Sorry for the inconvenience)

Withdrawn reply

I think (but I admit that I’m really not sure and could be completely wrong :sweat_smile: ) that this is what you were looking for :blush: (for the Amount formula in your [How much each owes what] table :

[Things that cost money].Filter(
  [Paid By].Name.Contains(thisRow.Owed) 
  [Shared Between].Name.Contains(thisRow.Payee)
  ).[Amount Each].Sum()

What it does is :

  1. Takes the table [Things that cost money] and look for rows (Filter()) where :

    • the Name of the person referenced in the field [Paid By] contains thisRow.Owed

    In other words, this creates a list of Items (a list of rows) from the table [Things that cost money] where the person who paid for the item is the Owed mentioned in thisRow.Owed


    • the Name(s) of the person(s) referenced in the field [Shared Between] contains thisRow.Payee

    Which, from the “previous” list of items will tell the Filter() to only keep for this row the Items where the person(s) who benefited from the items is the Payee mentioned in thisRow.Payee

  2. Now that we have that final list of items, all that’s left is to get and sum to the [Amount Each] :blush:

But : This will only work in your actual doc if and only if, in your table [How much each owes what] you setup your Payees like in this screenshot (i.e.: duplicate the Payee for each Owned)

I sadly don’t have the time right now to dig deeper :pensive: … But if I’m not completely wrong here (sorry if that’s the case), I hope it helps a little :blush:

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.