Work your Excel Wizardry
August 27, 2007 2:04 PM Subscribe
Calling Excel wizards: Splitting groceries in Excel between 3 people
I own a house, and share it with 2 others, my roommates/tenants. I know there are lots of bill-splitting websites out there, but I like using excel to calculate what everybody owes on utilities and such. It's been easy so far; I pay all utilities, and then bill each roommate their rent + 1/3 of the utilities. Simple, right?
Well, now we're going with communal food. Everyone buys groceries, throws their reciepts in a jar, and at the end of the month we settle up. What I want is a formula setup in Excel that allows me to punch in "I paid X, Joe payed X2, and Jim paid X3." Excel then spits out "You owe Joe Y, and Jim owes Joe Y2 and you Y3."
How?
I own a house, and share it with 2 others, my roommates/tenants. I know there are lots of bill-splitting websites out there, but I like using excel to calculate what everybody owes on utilities and such. It's been easy so far; I pay all utilities, and then bill each roommate their rent + 1/3 of the utilities. Simple, right?
Well, now we're going with communal food. Everyone buys groceries, throws their reciepts in a jar, and at the end of the month we settle up. What I want is a formula setup in Excel that allows me to punch in "I paid X, Joe payed X2, and Jim paid X3." Excel then spits out "You owe Joe Y, and Jim owes Joe Y2 and you Y3."
How?
This is kinda hacked together and won't scale to more than the three people you describe in your question, but here's a google spreadsheet (can be saved as excel) that I think works for what you need.
posted by jacquilynne at 2:56 PM on August 27, 2007
posted by jacquilynne at 2:56 PM on August 27, 2007
Response by poster: Jacqu..., is there something i can drop in there to figure out what jim and joe owe each other, thereby giving the breakdown for everybody?
Oh, and you guys are awesome
posted by craven_morhead at 3:07 PM on August 27, 2007
Oh, and you guys are awesome
posted by craven_morhead at 3:07 PM on August 27, 2007
There should never be a scenario with more than 2 transactions between 3 people.
The first X owes Y line figures out who owes the most money, and who is owed the most money, and has a payment between them.
The second X owes Y line then figures out if the third person, who wasn't involved in the first transation is owed money (which must, in that case, comes from the person who paid out in the first line) or owes money (which must, in that case, go to the person who got paid in the first line) and how much it is.
That's why it really doesn't scale past 3 people -- you'd have to keep adding ever increasingly complicated if statements to figure everything out. But for three people, either one person is below the average and the other two are above it (so both pay the first person) or two people are below the average and one is above it (so he pays both of them). There's a special case where one person is bang on the average, and the spreadsheet handles that, as well.
posted by jacquilynne at 3:29 PM on August 27, 2007
The first X owes Y line figures out who owes the most money, and who is owed the most money, and has a payment between them.
The second X owes Y line then figures out if the third person, who wasn't involved in the first transation is owed money (which must, in that case, comes from the person who paid out in the first line) or owes money (which must, in that case, go to the person who got paid in the first line) and how much it is.
That's why it really doesn't scale past 3 people -- you'd have to keep adding ever increasingly complicated if statements to figure everything out. But for three people, either one person is below the average and the other two are above it (so both pay the first person) or two people are below the average and one is above it (so he pays both of them). There's a special case where one person is bang on the average, and the spreadsheet handles that, as well.
posted by jacquilynne at 3:29 PM on August 27, 2007
What may not be clear, just from glancing at the spreadsheet is that the only place I *typed* Jim and Joe was in the top line. The Jim owes you, Joe owes you stuff at the bottom is automatically determined by the spreadsheet. Play with the numbers at the top and you'll see what I mean.
posted by jacquilynne at 3:32 PM on August 27, 2007
posted by jacquilynne at 3:32 PM on August 27, 2007
I fixed an absolute value in one of the formulas and adjusted something else, so if you saved it off before, you might want to grab a new copy.
posted by jacquilynne at 3:55 PM on August 27, 2007
posted by jacquilynne at 3:55 PM on August 27, 2007
I use BillMonk for this. Unlimited people, quick and easy. Been using it for a good deal of time now with different roommates.
You can cheat like I do and use throw-away gmail accounts as your roomates - so they never have to use the site.
https://www.billmonk.com/
posted by Lizc at 4:36 PM on August 27, 2007
You can cheat like I do and use throw-away gmail accounts as your roomates - so they never have to use the site.
https://www.billmonk.com/
posted by Lizc at 4:36 PM on August 27, 2007
« Older Turn off translucent dragging in Flash CS3 on OS X | Now you're scanning with... well, with what? Newer »
This thread is closed to new comments.
Three columns, first has names, second you put the amounts of the reciepts into, third has this formula: =b1-(B$4/3) and in B4, you need to put = sum (b1:b3)
When I tested, people ended up with a negative or positive figure, so basically, if you have a negative number, you throw money in the jar, if you have a positive result, you take money out.
You could make it a little groovier by giving each person a spreadsheet to enter their totals in and grabbing that total for the B column.
posted by b33j at 2:16 PM on August 27, 2007