Kitty help!

[Stfnah]

Hello,

Living in a house with 7 people has many perks, but working out a completely fair kitty system is not one of them.

We all cook for each other about once a week.
We note down who paid for the food, how much it cost and who was present out of the 7 people living here.

We have all tried to figure out an excel spreadsheet to work out how much money people owe/are owed at the end of each month but we havn't got the excel skills to make it work.

Could someone tell me how to make a spreadsheet work so that all I need to input is - WHO COOKED - HOW MUCH IT COSTED - WHO WAS PRESENT providing the output - WHO IS OWED WHAT AT THE END OF THE MONTH.

an example of a scruffy and incomplete kitty cooking sheet to give you an idea.. http://i121.photobucket.com/albums/o219/stefner100/dissscan017.jpg

Any help appreciated!

Many thanks , Stef

 

[mikerickson]

I'm guessing the goal is "at the end of the month who owes money to whom?"

Who paid for the cost of a meal? The cook?

Am I correct that only those present are expected to pay for the cost of that meal?

Is the cook supposed to pay for their share of the cost or is that "paid" for by their labor of cooking?

Is there a communal money pot, into which those who owe can pay and out of which those who are owed can get reimbursed?

 

[rconverse]

Something like this should get you started. It's a lot simpler than you think! Welcome to the board.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Day	Chef	Person1	Person2	Person3	Person4	Total Cost	Cost Per Person
2	Monday	Person1	X	X	X		5	1.67
3	Tuesday
4	Wednesday	Person2		X	X	X	10	3.33
5	Thursday
6	Friday	Person3	X		X	X	11	3.67
7	Saturday
8	Sunday	Person4		X	X		14	7.00
Sheet1

HTH,
Roger

 

[Stfnah]

Fantastic!
Such a quick response, thank you.

I have just placed the formula into the spread sheet and now I have the conditional 'cost per head' values which is very helpful.

There's still a lot I need to work out though. Ill keep trying ..

@Mike -

I'm guessing the goal is "at the end of the month who owes money to whom?"
- yes, this is the goal

Who paid for the cost of a meal? The cook?
- the cook always pays for the meal

Am I correct that only those present are expected to pay for the cost of that meal?
- yes, only those present are asked to pay towards the meal. (as quite often people are away for a week or more )

Is the cook supposed to pay for their share of the cost or is that "paid" for by their labor of cooking?
- the cook pays for their equal cost of the meal. they do not get paid for cooking.

Is there a communal money pot, into which those who owe can pay and out of which those who are owed can get reimbursed?
- no, there is no communal pot. we all pay with our own money and use this system (table) to work out after a month (or two) who owes whom what.

Many thanks, Stef

 

[rconverse]

You can just add a sumif formula at the end of the month.

Excel Workbook
	A	B	C	D	E	F	G	H	I
23	Day	Chef	Person1	Person2	Person3	Person4	Person5	Total Cost	Cost Per Person
24	Monday	Todd	x	x		x	x	5	1.25
25	Tuesday
26	Wednesday	Stef	x		x		x	8	2.67
27	Thursday
28	Friday	Errin	x	x			x	12	4.00
29	Saturday
30	Sunday	Ryan		x		x		14	7.00
31	Monday	Todd		x			x	10	5.00
32	Tuesday
33	Wednesday	Stef	x	x	x	x	x	8	1.60
34	Thursday
35	Friday	Errin	x	x			x	13	4.33
36	Saturday
37	Sunday	Ryan		x	x	x		11	3.67
38			13.85	26.85	7.93	13.52	18.85
3000

HTH,
Roger

 

[Stfnah]

The SUMIF and $x$x:$x pieces were what I needed.

(I had just finished creating a table simply using a IF for every single box before you replied! but this coding makes the table much more flexible)

If I can upload it to googledocs I will show you what I have come up with at some point.

Again thank you so much!
Working it out by hand caused many head aches in our house so I assure you this table will go to good use!

Stef

 

[Stfnah]

http://spreadsheets.google.com/ccc?key=0AltZe6Jb5h60dFRtTXg5Q1RWUHFJZG4zLVI1OVJ2SVE&hl=en_GB

 

[rconverse]

Looks good and you're welcome.