Let me explain my problem... (Roommate Accounting)
 

Okay, I think the best way to do this is to have me explain what I'm
trying to do.

Next year I'm living with 5 others, and I want a sheet to calculate our
living expenses, and to figure out how much we owe each other. I have
most of the sheet done, each row is an expense, where we can put in
'Grocery Bill' for example, put in how much it is, who paid for it, and
the parties involved in the purchase. To the right of that I have
columns for how much each person owes for that row. My problem:

I need a way to take the amount of each purchase, break it up by the
number of names in the 'parties involved' field, and fill in the
appropriote amount for each person's column.

So for example, we spend $100 at the grocery store, the bill is paid
for by Travis, and the parties involved in the purchase are Travis,
Leo, and Dan. The sheet must recognize that the bill must be split 3
ways, and then know to add $33.33 to Travis', Leo's, and Dan's column
for the amount owed.

I've attached the XLS to make this a little bit more easy to
understand. Feel free to edit it and repost. I'm also more than happy
to post a final version of the file, as I'm sure I'm not the first
person to want a sensable way of handling roommate accounting.

Thanks in advance!


+-------------------------------------------------------------------+
|Filename: Roommate Accounting.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5163 |
+-------------------------------------------------------------------+

--
IanMcGreene
------------------------------------------------------------------------
IanMcGreene's Profile: http://www.excelforum.com/member.php...o&userid=37247
View this thread: http://www.excelforum.com/showthread...hreadid=569621

Let me explain my problem... (Roommate Accounting)
 

IanMcGreene wrote:
Okay, I think the best way to do this is to have me explain what I'm
trying to do.

Next year I'm living with 5 others, and I want a sheet to calculate
our living expenses, and to figure out how much we owe each other. I
have most of the sheet done, each row is an expense, where we can put
in 'Grocery Bill' for example, put in how much it is, who paid for
it, and the parties involved in the purchase. To the right of that I
have columns for how much each person owes for that row. My problem:

I need a way to take the amount of each purchase, break it up by the
number of names in the 'parties involved' field, and fill in the
appropriote amount for each person's column.

So for example, we spend $100 at the grocery store, the bill is paid
for by Travis, and the parties involved in the purchase are Travis,
Leo, and Dan. The sheet must recognize that the bill must be split 3
ways, and then know to add $33.33 to Travis', Leo's, and Dan's column
for the amount owed.

I've attached the XLS to make this a little bit more easy to
understand. Feel free to edit it and repost. I'm also more than
happy to post a final version of the file, as I'm sure I'm not the
first person to want a sensable way of handling roommate accounting.

Thanks in advance!



Hi Ian,

I modified a little bit your file, adding 1 column for each one to split
your column "Parties involved", then using three IF and a COUNTIF it was
very simple...

Here you can download the modified file:

http://rapidshare.de/files/31196623/...ified.zip.html

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Let me explain my problem... (Roommate Accounting)
 

Franz Verga wrote:
IanMcGreene wrote:
Okay, I think the best way to do this is to have me explain what I'm
trying to do.

Next year I'm living with 5 others, and I want a sheet to calculate
our living expenses, and to figure out how much we owe each other. I
have most of the sheet done, each row is an expense, where we can put
in 'Grocery Bill' for example, put in how much it is, who paid for
it, and the parties involved in the purchase. To the right of that I
have columns for how much each person owes for that row. My problem:

I need a way to take the amount of each purchase, break it up by the
number of names in the 'parties involved' field, and fill in the
appropriote amount for each person's column.

So for example, we spend $100 at the grocery store, the bill is paid
for by Travis, and the parties involved in the purchase are Travis,
Leo, and Dan. The sheet must recognize that the bill must be split 3
ways, and then know to add $33.33 to Travis', Leo's, and Dan's column
for the amount owed.

I've attached the XLS to make this a little bit more easy to
understand. Feel free to edit it and repost. I'm also more than
happy to post a final version of the file, as I'm sure I'm not the
first person to want a sensable way of handling roommate accounting.

Thanks in advance!



Hi Ian,

I modified a little bit your file, adding 1 column for each one to
split your column "Parties involved", then using three IF and a
COUNTIF it was very simple...

Here you can download the modified file:

http://rapidshare.de/files/31196623/...ified.zip.html


I added some formatting, Validation data on column C and the possibility to
change interactively the names in columns E:J having all the formulas
updated.

Here is the link:

http://rapidshare.de/files/31199273/...ber_2.zip.html

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy