How to: sharing trip expenses using excel

Yesterday we were calculating our Washington trip expenses and it occurred to me that if there is an excel template where I could enter the trip expenses and who paid what to find out how much we need to pay up / collect, it would be great. I looked around for few seconds, couldn’t find anything. So I went ahead and built expense sharing worksheet on google docs. Feel free to use it for sharing your trip / party / picnic / apartment expenses.

Here is how I have done it.

• First I have created a list of people who need to share. Just for simplicity I have limited the no to 10. You can edit the excel and change it if you want.
• Then I have created an expense table like the one shown below. The requirements for this are simple, (1) should be able to capture descriptions of each spending (2) should be able to specify who footed the bill (3) should be able to mention the amount (4) should be able to exclude people from sharing a particular expense. Again, for simplicity sake I have limited the number of people who can be excluded from sharing an expense to 4. You can always edit this and change the formula accordingly.

  

• Now next to the list of people I have added 3 columns to show (1) how much that person has already paid (2) how much is his/her share (3) what is the difference.
• The formula for (1) how much already paid is straight forward sumif(), the formula for a particular person’s share is something like this,
  

  If the row has a person
  (total expenses / no.of people) – (total expenses excluded for this person / no. of people)
  else “0”

  The actual formula involved sumif() over the four columns since each expense can be excluded for maximum of 4 people.

• Once I have entered data the output looked something like this,
  
  Hence both Yerry Jang and Dointy Paired Hilbert need to collect 237 $ and 90 cents from Barren Wuffet, Gill Bates and Beff Jezos put together. Not bad 😉

How to use it?
Just save the expense sharing excel sheet from google doc [or download the expense sharing excel sheet] to your machine / account. Enter the expenses in columns B,C and D. Enter the people names in column L. Incase you need to exclude any one from a particular line item, just enter their number (it will be next to the person’s name in column K]. As you enter the expense details, the Column O displays the actual dues.

Feel free to comment / extend / share / suggest. Your turn…