Splitting Costs With Friends Template

This tool was inspired by this blog post I wrote a few years back where I first built the tool in Google Sheets. Not as automated and robust as I would like, but take a look at the tool here: :point_right: Splitting Costs With Friends :point_left:

Also just did a video tutorial on the tool with some pros/cons at the end:

11 Likes

This is awesome @Al_Chen! Really interesting use case of the product (would love to snag the template!).

On the pros/cons list - you mentioned the formulas in the Cost Breakdown section. Any reason you chose doing these calcs in the canvas vs. in a table that summarizes the data via Lookup()? Could simplify the calculations a bit (not requiring copy-paste of formulas, and you could still get a visually appealing bulleted list at the end by concatenating the fields and using .bulletedlist() in the canvas.

Liked the form hack for entering costs! Need to figure out a cleaner way to implement that in the product :).

1 Like

@evan Sure would be happy to share, I guess request access to the template and I can grant access?

My original architecture was to have a summary table showing each person’s costs and how much they owe. I couldn’t find a suitable formula to figure out how much each person owes given the structure of my Costs table so therefore added the extra columns to my Costs table and then wrote the formulas to sum up those costs on the canvas.

In the Who Participated column of the Costs table it’s a Lookup From Table format allowing multiple selections (letting you select multiple friends in your group). The formula would have to look through each row in this column to see if the given name exists. I was thinking about asking the community to see if they could think of the formula for this.

I’ve seen the form hack used in multiple places so it appears to be the go-to hack for entering data into a table.

Nice @Al_Chen! Over the holidays I went through a similar exercise (needing to split some costs with family) and turned it into a yet-to-be-released template: Splitting shared expenses

You should be able to open that to view – or create a copy if you want to pull it apart.

1 Like

Awesome @matthudson I couldn’t figure out the formula for the “Owe” column. I knew it involved using CONTAINS() but didn’t think about using it inside a filter. I ended up just creating separate columns for each person in the group and using the IN() formula to figure out if the cost was relevant to that person for each row. When in doubt, I know now to throw things into the filter.

@Al_Chen Very well done! Cool doc! :slight_smile:

For entering data into tables without cluttering them too much I’ve used time based formulas.

So you’d first set a variable like:

`= Now() - Minutes(5)`

And call that for example five-minutes-ago, or whatever time interval you find appropriate.

Then in the filter for “Enter Costs Here” you would set it to:

`Created(thisRow)>=[five-minutes-ago]`

That way, your row will not immidiately disappear after writing it, which could be confusing to users and you can still keep that table clean by filtering out older entries.

3 Likes

Good point @William_Larsten! I tried adding this as a condition to the view so the filter formula looks like this:

Created(thisRow)>=[15 Seconds Ago]&&Item.IsBlank()

The issue is that the Item.IsBlank() condition still filters out the row immediately after you enter in the data. The reason why I like this condition is because if the user accidentally clicks in the view but decides not to add any data, this action will create an empty row in the master table. Empty rows in the master table doesn’t appear to affect counts or sums but just clutters the master table with empty rows. I suppose the tradeoff would be an uncluttered master table (but with immediate filtering for the user upon entering data) vs. a nice delay for the user as they enter data with the possibility of a cluttered master table.

3 Likes

@William_Larsten, i don’t figure out how and where you set the variable ? do you have done example ?

@Antoine_Guicheteau

You can type the equal sign, “=”, anywhere in the document and write the formula there. Then click the downward sign and give the formula a name that you can reference elsewhere.

23

1 Like

Thanks for providing the answer @William_Larsten! @Antoine_Guicheteau, you can also read the full help article on naming formulas here :grinning:

1 Like

Thx a lot for the trick ! :pray: