Splitting Group Costs with Spreadsheets

After a beach outing, ski trip, or joint roommate purchase, we just might need to split shared expenses among a group. A spreadsheet can serve as an efficient and practical tool to do just that! See this framework live in action in our 6-minute video below.

Video Tutorial

How does this work?

Let’s imagine you take a trip with 4 of your friends. Throughout the trip, each person paid for something for the group, whether it be meals, amusement park tickets, transportation, etc. By the end of the trip, you need to figure out a way to determine who owes what to whom so that all group costs are shared equally.

In our framework, all group members first input information – specifically, what the shared expense was and who paid for it, as shown to the right. A benefit of Google spreadsheets is that it is an online, real-time collaborative platform. This enables all members of your group to simultaneously access and add or edit data!

As such, spreadsheets are a useful way to input, organize, and keep track of data, which in our case includes the expense description, amount, and the person who paid the expense.

Not only do spreadsheets organize data, they can be a useful in solving problems. The problem or question we want to answer is: At the end of the trip, who owes who what, so that all group expenses are shared evenly among all?

Here the SUMIF function plays an important role in this calculation. As seen below, the template is set up to provide the following output: the expense amount per person (shown in the far right), and the amount each person either owes or expects to receive (shown on the bottom row).

So here, we see that Erin has contributed $120 of the $244 total group expenses per person.  Therefore, she owes an additional $124 to her friends on this trip. Specifically, she owes $56 to Anna, $41 to Beth, $21 to Cathy, and $6 to Donna. Once completed, each person shared all group expenses equally on this trip.

Spreadsheet Challenge

Before downloading our spreadsheet or watching the video tutorial, try to figure out how to create this template! As always, there are numerous ways to solve this problem. In our approach, the SUMIF function was the key element. To view a video tutorial on this function, select here: SUMIF

Resources

• Related Spreadsheet Template: Splitting Group Costs with Spreadsheets
• Data Validation Video Tutorial: Data Validation Video 

 IMPORTANT: To edit the spreadsheet, go to ‘File’ and select, ‘Make a Copy’. This will automatically save a copy on your Google drive.