How to: sharing trip expenses using excel

Posted on January 25th, 2008 in hacks , Learn Excel - 17 comments

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.

Sharing Trip / Party / Picnic / Apartment expenses using Excel

  • 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.

    Sharing Trip / Party / Picnic / Apartment expenses using Excel

  • 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,
    Sharing Trip / Party / Picnic / Apartment expenses using Excel
    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…

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

17 Responses to “How to: sharing trip expenses using excel”

  1. Sabs says:

    Hi , Dropped in here through india uncut. Execllent effort to capture the expense statement. Have personally done this many times, but never had the patience to pull out a format like this.

    Just have one small query. As per your example , the total amount spent is 714$ but if I add the shares , it adds upto 686$ only. Shouldn’t the amount spent and shares for each person match ?

  2. Chandoo says:

    @Sabs, welcome to PHD, thanks for appreciating my post.

    I am sorry, but when I try to add the shares {139, 137, 142, 145, 151 } it adds up to 714. Are you referring to the image or one of the excel sheets. Let me know if there is some confusion?

    • Sara says:

      Hi Chadoo, great work indeed! I have filled in the sheet but have a problem though. I get the #NAME error throughout all colum I and consequently in N and O. Any tips?
      Thanks!
       

  3. Sabs says:

    Sorry about bombarding you. Your version in the download is different from what you have in google docs. The google doc version and the screen shot in your post doesn’t have the problem I mentioned.

  4. Chandoo says:

    @ Sabs, hey thanks for pointing out the mistake. Let me update that faulty excel. You have a good day :)

  5. Mahin says:

    Hi Chandoo:

    I personally developed on for myself and made it public. you can find it here http://www.screeperzone.com/expensesharer . It reduces the number of money/check transactions . thought it might be useful. Yours is good too.. thanks!

  6. Mikael Grave says:

    Hi Chandoo,
    Another simple online solution to handle shared expenses:
    http://shortreckonings.com
    The user interface was carefully thought to make expense entry as quick as possible. For example, the tool does auto-complete descriptions so that you never need to enter twice recurring expense descriptions.
    You can enter non-even split, for example fixed amounts, percentages or any kind of sharing weights.
    Enjoy!

  7. Steve says:

    Hi,

    I found yet another free tool for managing shared expenses:
    http://www.expensesharing.com

    This one differs that it allows to invite other colloborants,
    and is easy to use. In addition it is capable to handle currency conversion.

  8. Bourne says:

    Thanks for the post.. this template is very helpful… http://www.screeperzone.com/expensesharer is also a very helpful application.takes an extra step to reduce the number of money transactions between participants.

  9. Sandeep says:

    Great effort!…I also struggled quite a bit to share expenses in a fair manner when I was staying with roomies. Being somewhat of a techie myself, I came up with this :

    http://sandypai1983.wordpress.com/2010/07/21/great-excel-tool-for-sharing-expenses/

    Hope you find this useful. Excel is amazingly handy and useful in all sorts of basic analytical stuff.

  10. vivek says:

    How can we use this for ruppes?

  11. Jeremiah Minifield says:

    In your example, if you exclude 1 person, the amount should increase not decrease. I can’t see how the exclusion works. Please explain. Thanks.

  12. Narendra Agrawal says:

    Hi,
    There is a small issue in the formula. It is actually more of an XL issue. In the example if you check the 15th item and see the per head calculation, you will realize that the final value is incorrect.

    Here in the end, in the denominator of formula it comes as “10-5-3″. XL instead of returning 2 returns 10-(5-3)=2. Hence u get a 18/2=2.3 instead of 18/2=9

  13. jm says:

    I think the formula in column I should be as follows:

    =D4/(10-COUNTA(WHO_ALL)-COUNT(E4:H4))

    Rather than COUNTIF(WHO_ALL, “”), which is counting blank rows in the WHO_ALL range, when it should be counting actual participants.

  14. Jeff Weir says:

    My god, Chandoo…here I am combing your blog archives in chronological order, and this is the first post on Excel on your blog, despite 3.5 years of blogging before this!

    And the titles of some of those earlier blogposts…oh my!

    I like!

Leave a Reply