Expense Splitter -

This happens to all of us. You are invented to a barbeque and someone buys the meat, another the beer and in the end you want to balance the payments so that everyone pitches in. This can be a complex math issue, but not any more! I built an expense splitter that you can use to balance the payment between your friends.

Joe paid for the burgers, Bill and Steve will pay him back (hopefully)

If you are more of a video person, check out the YouTube for it:

https://youtu.be/kYH7penJRVM

Still with me? Good, here is the written description on how to build it. The file is split into 4 parts:

Payments sheet – this is the main sheet, the front end of the file where the user inputs data and sees the output.
Balance sheet – back end sheet used for calculations.
Auto balance sheet – this sheet helps build the recommended payments between the members.
List – dynamic drop down list for the selection of who paid in the payments sheet.

Payments sheets :

Updating the file is very simple, the user needs to fill out three columns.

Payment – what was the money spent on.
Amount
Paid by – this would be based on the possible people.

300 bucks for Burgers??? Show me the receipt please!

The user needs to add the people that participate + which member should pay for the payment. Assuming not everything is 100% shared.

Add a V if that person should pay for that line, add names to the right to add members.

Within the payments sheet you immediately see two outputs:

Payment Plan – this is the output of all the sheets, it shows you a recommendation on how to balance the payments within the members.
Personal totals – each member can see how much they spent, how much they should pay and the balance.

On top each member can see their balance.

Functions used for this sheet:

Amount per person – divide the payment per #of people, needed for the final calculations. I am using Countif (range, “V”). So the result is the number of cells with a V in them.

Total paid per person – SUMIF($E6:$E9999,G$5,$D6:$D9999). Sumif using the name of the person who paid as the criteria.

Total participated – -SUMIF(G6:G9999,”V”,$F6:$F9999). Sumif for lines where there is a V for each person.

Payment plan – I’m using offset to parse the range from the auto balance sheet. OFFSET(‘Balance Sheet’!K2,,,COUNTIF(‘Balance Sheet’!J:J,”>0″),1). The countif helps to parse only the needed lines.

Balance sheet:

The sheet has two main tables – total balance table (left side) and payments table summary (right side).

A>E total balance table, G>K payments table summary

The balance table pulls the paid, owed per person. This is taken from the payments sheet using index + match. I want to pull the summary amounts from the top lines to this sheet. The balance value is the difference between the two figures. To pull the name I am using offset with a running index.

The payments table is pulling the data from the auto balance sheet and basically only shows rows for payments between people. The from, to and payments are pulled using index + match. The payment column uses & to build a text that will be shown in the payments sheet.

Using & to combine data and text for the messages

Auto balance sheet:

This sheet is the main algorithm that builds the payments between the people. The method is very simple, I am building all the combinations between the people and checking who needs to pay and who is owed. While building the combinations I am deducting any payments that are created in top rows.

Step 1 – building all the combinations, this is done with 3 functions. From# , To# and #of people. The key is the from# goes from 1 to 6, once it reaches 6 is goes back to 1.

To# goes from 1 to 6, once the from# reaches 6 is grows +1.

#of People which is the trigger to change combinations, is simply a Counta for the payments line showing the people. COUNTA(Payments!$5:$5)-5.

Step 2 – add the from , to names . This is done using offset and the index based on the from# or to#.

OFFSET(Payments!$F$5,,A2,,).

Step 3 – add the Needs to pay, Owed and payment columns.

I’ll start with the payment columns – this is the min between the absolute value of needs to pay (which is negative) and the owed. Using min guarantees that the payment will not exceed what’s owed or what this person needs to pay.

Needs to pay – minimum between what this person needs to pay (vlookup from balance sheet) and the running payment he/she maid (running sumif with a dollar for the start of the range).