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

[IanMcGreene]

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

Next year I'm living with 5 others, and I want a sheet to calculate ou
living expenses, and to figure out how much we owe each other. I hav
most of the sheet done, each row is an expense, where we can put i
'Grocery Bill' for example, put in how much it is, who paid for it, an
the parties involved in the purchase. To the right of that I hav
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 th
number of names in the 'parties involved' field, and fill in th
appropriote amount for each person's column.

So for example, we spend $100 at the grocery store, the bill is pai
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
ways, and then know to add $33.33 to Travis', Leo's, and Dan's colum
for the amount owed.

I've attached the XLS to make this a little bit more easy t
understand. Feel free to edit it and repost. I'm also more than happ
to post a final version of the file, as I'm sure I'm not the firs
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
+-------------------------------------------------------------------

 

[Franz Verga]

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/Roommate_Accounting_-_modified.zip.html

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Franz Verga]

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/Roommate_Accounting_-_modified.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/Roommate_Accounting_-_modified_-_version_number_2.zip.html

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy