Finishing my "who ows who": how to distribute difference in who paid what?

[annekrijn]

Hi all,
I've made a nicely working "who ows to whom" spreadsheet for paying for weekends away etc in a group. Works well, I just can't figure out the last bit.
I got as far as calculating the amount of money owed by everyone, based on who paid for things and the ratio everyone will contribute to every bill.
Now I want Excel to calculate who ows how much to whom, depending on the different amounts spent by different people.

So for example:

Nick paid 10
Kate paid 20
Oliver paid 0.

Everyone's total is 30/3=10, so Oliver has to pay 10 to Kate. It's that last bold line that I want Excel to figure out.

How?

Thanks in advance! I'm very much willing to share my spreadsheet, if anyone's interested. It's no rocket science, or in any way spectacular, but definitely useful.

[shukla.ankur281190]

Row\Col	A	B	C
1	Name	Amount	+/-
2	Nick	10	0
3	Kate	20	10
4	Oliver	0	-10
5
6	Total	30
7	Each Person	10
8
9		So Oliver has to pay 10 to Kate

B6

Please Login or Register  to view this content.

B7

Please Login or Register  to view this content.

C2

Please Login or Register  to view this content.

Drag down C2 formula.

B9

Please Login or Register  to view this content.

Format it as bold

[annekrijn]

Wow, I can't get this to work yet, but I'm going to try to fit it correctly into my sheet. Thanks!

[shukla.ankur281190]

Thanks for feedback
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[annekrijn]

OK, I can't get it to work. Here's my spreadsheet, I translated and emptied it a bit.
Could you show me how to add your solution? In my sheet I have 5 persons sharing bills, not 3. I don't think that would make a difference?

In the end, it comes down to 3 persons owing money to 2 persons. I'd like Excel to figure out who owes who and how much.

[shg]

You might look at the one at https://app.box.com/shared/7vb4giq2x7

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
9	1. Individual or Group	2. Shares	3. Amount Paid	4. (Owes) / Is Owed	X	Joe, Julie, & Caroline	Greg & TT	Ashley & Chris	John & Patter	Torey, Julia & George	Gene & Pat	Christine & Eric	Amy & Patrick	Kelly & Gerick	Stephen, Joanne, & Kate	LB & Kevin
10	Joe, Julie, & Caroline	3	$ (100.00)	$ (122.00)				$ 122.00
11	Greg & TT	2	$ -	$ (148.00)				$ 131.00	$ 17.00
12	Ashley & Chris	2	$ (401.76)	$ 253.00
13	John & Patter	2	$ (184.00)	$ 36.00
14	Torey, Julia & George	0	$ (50.00)	$ 50.00
15	Gene & Pat	2	$ -	$ (148.00)					$ 19.00	$ 50.00		$ 52.00	$ 27.00
16	Christine & Eric	2	$ (200.00)	$ 52.00
17	Amy & Patrick	2	$ (349.14)	$ 201.00
18	Kelly & Gerick	2	$ (100.00)	$ (48.00)									$ 48.00
19	Stephen, Joanne, & Kate	3.5	$ (187.79)	$ (72.00)									$ 72.00
20	LB & Kevin	2	$ (92.00)	$ (56.00)									$ 54.00